MichelleA
July 8, 2020, 1:08pm
1
Hi!
I have been trying to tweak some of the formulas I created and ones that the Monday support team have given.
Issue is I wanted to count the tenure of an employee.
example:
Formula used so far are:
This gives an answer of 1yr and 9 months instead of just 8 months
This gives an Illegal Formula Error12,0)),” months”) *
Here are more examples using the first formula
Where should I tweak or is should I use a different formula?
1 Like
MichelleA
July 8, 2020, 1:36pm
2
Thank you Monday Community for the support! They’ve responded to my emails quickly!
Here it is people!
CONCATENATE((ROUNDDOWN((DAYS({End Date},{Start Date})/30.4)/12,0))," years"," “, (ROUNDDOWN((((DAYS({End Date},{Start Date})/30.4)/12)-(ROUNDDOWN((DAYS({End Date},{Start Date})/30.4)/12,0)))*12,0)),” months")
Hope this helps!
rosanacamara
December 30, 2020, 8:29pm
3
This formula is wrong. It didn’t work for me. Can you tell if you have an error?
Hey @rosanacamara , thanks for posting! How can you tell it’s not working for you? Is it giving you a red exclamation point or some other kind of error message? I ask because I’d like to isolate the issue here. I would also double check to make sure your column names match what is in the squiggly brackets of the formula exactly.
We’re more than happy to help you get the formula figured out if you email us at support@monday.com as well
rosanacamara
January 4, 2021, 2:39pm
5
Shure! Look my prints:
The formula:
Hey @rosanacamara , thanks for getting back to me!
Can you try using this formula?
CONCATENATE((ROUNDDOWN((DAYS(TODAY(),{Start Date})/30.4)/12,0))," years"," “, (ROUNDDOWN((((DAYS(TODAY(),{Start Date})/30.4)/12)-(ROUNDDOWN((DAYS(TODAY(),{Start Date})/30.4)/12,0)))*12,0)),” months")
It should work for you. If not, write into us at support@monday.com and we’ll be happy to help you out there
rosanacamara
January 6, 2021, 4:15pm
7
Hi, thanks for the feedback, but it didn’t work either
JCorrell
January 11, 2021, 7:57pm
8
For those of you who are watching, the problem with the last formulas as given are that they have been copied and pasted directly. When pasting formulas with quotes in them, it is best to precede the text with 4 spaces so that it will be treated as “preformatted text”. Otherwise the “format processing” will change some of the quotes to “smart quotes” which will not work in the monday formulas.
I got bit by this a few times before I finally figured it out.
Notice in the text below how the third quote mark after " years" is slightly different from the given formula above…
CONCATENATE((ROUNDDOWN((DAYS({End Date},{Start Date})/30.4)/12,0))," years"," ", (ROUNDDOWN((((DAYS({End Date},{Start Date})/30.4)/12)-(ROUNDDOWN((DAYS({End Date},{Start Date})/30.4)/12,0)))*12,0))," months")
1 Like
rosanacamara
January 13, 2021, 7:26pm
9
Hi @JCorrel , thanks. Support sent me the code below and it worked!
CONCATENATE((ROUNDDOWN((DAYS({End Date},{Start Date})/30.4)/12,0))," years"," “, (ROUNDDOWN((((DAYS({End Date},{Start Date})/30.4)/12)-(ROUNDDOWN((DAYS({End Date},{Start Date})/30.4)/12,0)))*12,0)),” months")
JCorrell
January 13, 2021, 7:56pm
10
Just to keep everyone else on the same page…
If someone was to copy and paste the formula text that you just shared, it would NOT work.
…,0))," years"," “ <— NOTICE THE SLANTED QUOTE MARK IN THE FIRST LINE
The text from support did not have that. But when it was pasted here the “formatter” changed it.
1 Like
Sultan
November 28, 2021, 6:25pm
11
Hello,
New here to monday.com
How we can add days to this formula?
What I am looking for is in the format:
Thanks…
1 Like
JCorrell
November 29, 2021, 3:50pm
12
@Sultan
CONCATENATE(
(ROUNDDOWN((DAYS({End Date},{Start Date})/30.4)/12,0))," years ",
(ROUNDDOWN((((DAYS({End Date},{Start Date})/30.4)/12)-(ROUNDDOWN((DAYS({End Date},{Start Date})/30.4)/12,0)))*12,0))," months ",
IF(DAY({Start Date}) <= DAY({End Date}), DAY({End Date}) - DAY({Start Date}) + 1, DAY({End Date}) + DAY(EOMONTH({Start Date}, 0)) - DAY({Start Date}) + 1), " days")
Note: all the formulas in this thread will return an error if either date is missing AND incorrect results if {Start Date} is > {End Date}.
Jim - Subscribe to The Monday Man Get Rid of Subitems
Sultan
November 30, 2021, 11:10am
13
JCorrell:
CONCATENATE(
(ROUNDDOWN((DAYS({End Date},{Start Date})/30.4)/12,0))," years ",
(ROUNDDOWN((((DAYS({End Date},{Start Date})/30.4)/12)-(ROUNDDOWN((DAYS({End Date},{Start Date})/30.4)/12,0)))*12,0))," months ",
IF(DAY({Start Date}) <= DAY({End Date}), DAY({End Date}) - DAY({Start Date}) + 1, DAY({End Date}) + DAY(EOMONTH({Start Date}, 0)) - DAY({Start Date}) + 1), " days")
Worked perfectly,
You’re The Monday Man
system
December 7, 2021, 11:10am
14
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.
