Formula to count Employee Tenure in years and months

Platform discussions
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:
Start Date (Date Column) : November 4, 2019
End Date (Formula Column: TODAY()

Formula used so far are:

  1. This gives an answer of 1yr and 9 months instead of just 8 months
    ROUND(DAYS({End Date}, {Start Date}) / 365, 0) & " years " & ROUND(MOD(DAYS({End Date}, {Start Date}), 365) / 28, 0) & " months"

  2. This gives an Illegal Formula Error
    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”)*

Here are more examples using the first formula
image

Where should I tweak or is should I use a different formula?

1 Like
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!

3

This formula is wrong. It didn’t work for me. Can you tell if you have an error?

4

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 :slight_smile:

5

Shure! Look my prints:

monday1
monday11447Ă—314 25 KB

The formula:
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")

monday2
monday21462Ă—677 48.1 KB

6

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 :slight_smile:

7

Hi, thanks for the feedback, but it didn’t work either :frowning:
I will email support, thank you.

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
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")

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
11

Hello,

New here to monday.com

How we can add days to this formula?

What I am looking for is in the format:
2 years 5 months 19 days

Thanks…

1 Like
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
Watch Our Latest Video: Get Rid of Subitems

13

Worked perfectly,
Thank you

You’re The Monday Man :smiley:

14

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.