Counting Months of Employment Formula

Platform discussions Special workflows & use cases
1

Hi!

I’m trying to use a formula to give the employment duration for current & past employee’s. If there is no end date set then I would like it to calculate up until the current date, otherwise It would calculate up to the end date.

IF({End Date}=“”,ROUND((Days(FORMAT_DATE(TODAY()),{Start date})/30),1),ROUND((Days({End Date},{Start date})/30),1))

I’ve tried the IF function, replacing the end results with 1 & 2 respectively and that works:
IF({End Date}=“”,1,2)

I can also get it to work by replace either of the end results with a single number;
IF({End Date}=“”,ROUND((Days(FORMAT_DATE(TODAY()),{Start date})/30),1),2)
IF({End Date}=“”,1,ROUND((Days({End Date},{Start date})/30),1))

When I put them all together it seems to break. Can anyone figure out why?

2

@Formula_Choice

It looks like you want the time in terms of months rounded to 1 decimal place.

This will work:

ROUND(YEARFRAC({Start Date},IF({End Date},{End Date},TODAY()))*12,1)

Jim - Subscribe to The Monday Man
Watch Our Latest Video: A Killer Combo: monday.com, Integromat & Google Sheets - Recurring Tasks Example

3

Hi Jim,

Thanks for getting back to me! The rounding function is working as intended. Perhaps I wasn’t clear enough on my main post.

The issue comes when adding the IF portion of the formula. If both Rounding formulas are contained in the IF Function then it spits out the Red Exclamation Mark unless the end date is filled.

I can’t figure out why the first part (below) works on its own, but not when paired with the other formula under the IF.
ROUND((Days(FORMAT_DATE(TODAY()),{Start date})/30),1),

4

@Formula_Choice

Ok, I’ll take another shot… give me the formula that doesn’t work. Exactly as in the formula column.

5

Thank you!

The formula is:
IF({End Date}="",ROUND((Days(FORMAT_DATE(TODAY()),{Start date})/30),1),ROUND((Days({End Date},{Start date})/30),1))

6

@Formula_Choice

monday functions are inconsistent with regard to giving an error on portions of formulas that are not needed; e.g.: IF(FALSE, 1/0, 1) will always give a divide by zero error. IF(FALSE, DIVIDE(1, 0), 1) will not.

IF(FALSE, DAYS{End Date}, TODAY()), 1) will return an “invalid parameter” error whenever {End Date} is empty.

In your formula, the portion that reads “…ROUND((Days({End Date},{Start date})/30),1)…” is returning an error whenever {End Date} is empty.

To avoid the error, you could change your formula to this:

IF({End Date}="",ROUND((Days(FORMAT_DATE(TODAY()),{Start Date})/30),1),ROUND((Days(IF({End Date}="","1/1/1",{End Date}),{Start Date})/30),1))
2 Likes
7

That worked perfectly, thank you so much!

1 Like
8

Didn’t know I needed this but it works perfectly! Thanks for the addition to my board.

9

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