Help

Getting DATETIME_DIFF to calculate correctly

Topic Labels: Formulas
934 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Tiffany_Elston
4 - Data Explorer
4 - Data Explorer

Hi there,
I’m trying to get a total number of years worked for my company, based on original Hire Date, Termination Date, Rehire Date, and Today(). I’ve been trying IF statements for a good day now, and while I’ve managed to get it to add up the years, it always rounds to the nearest whole number, even if it adds the decimals .00 after it.

Not every employee has a termination date or a rehire date, but some do. I need the formula to determine whether fields are missing and calculate based on the information it has. IE: (today - rehire date) + (termination date - hire date) or if fields are missing, to calculate accordingly.

This is what I have so far:

=IF({Hire Date} = BLANK(), 0, IF({Term. Date} = BLANK(), DATETIME_DIFF(TODAY(),{Hire Date},'y'), IF(RehireDate = BLANK(), DATETIME_DIFF({Term. Date},{Hire Date},'Y'),SUM(DATETIME_DIFF(TODAY(),RehireDate,'Y'),DATETIME_DIFF({Term. Date},{Hire Date},'Y')))))
(I can’t figure out how to get the code to display properly, sorry)

I’m not sure where I’m going wrong. I’ve got the format as a decimal with two places, but when the employee has worked for 10.91 years it still shows as 10.00.

Any input would be greatly appreciated, as I’m getting more gray hairs from this than my kids give me!!
Thanks a bunch,
Tiffany

2 Replies 2
Tiffany_Elston
4 - Data Explorer
4 - Data Explorer

Thought I had it, but it’s calculating the space between the rehire date and termination date as part of the total. Nuts.

Tiffany_Elston
4 - Data Explorer
4 - Data Explorer

Managed to get something to work. Hope this helps somebody else someday, even if the formatting isn’t correct.

(IF({Hire Date} = BLANK(), 0, IF({Term. Date} = BLANK(), DATETIME_DIFF(TODAY(),{Hire Date},'d'), IF(RehireDate = BLANK(), DATETIME_DIFF({Term. Date},{Hire Date},'d'),SUM(DATETIME_DIFF(TODAY(),RehireDate,'d'),DATETIME_DIFF({Term. Date},{Hire Date},'d')))))) / 365