IF Function
Overview: Trying to calculate the years of service for an employee sheet that contains two columns:
DOH = Date of Hire
DOT = Date of Termination (Note: most values in this field are blank)
Unable to figure out how to use the IF function when I'm comparing a Date of Termination (DOT) field that is populated to allow the if true & if false formulas to properly calculate.
With the formula below, if will calculate if the DOT is empty, but give an #INVALID OPERATION response if there is a value (i.e. date) in the DOT field.
=IF(DOT3 > 0, (DOT3  DOH3) / 365, (TODAY()  DOH3) / 365)
I've tried using ISBLANK with similar results. Not sure what I'm missing here.
Best Answer

Hey Bert,
Using column titles DOT, DOH, Today (these characters) I came up with the following  which worked for me.
If you have these 3 same column titles (make sure your calculation column isnt set as date) this formula you can likely simply copy from here and paste in:
=IF(ISBLANK(DOT@row), (Today@row  DOH@row) / 365, (DOT@row  DOH@row) / 365)
If it works dont forget to right click cell with formula and "set to column"
Let me know what you think.
Answers

Hey Bert,
Using column titles DOT, DOH, Today (these characters) I came up with the following  which worked for me.
If you have these 3 same column titles (make sure your calculation column isnt set as date) this formula you can likely simply copy from here and paste in:
=IF(ISBLANK(DOT@row), (Today@row  DOH@row) / 365, (DOT@row  DOH@row) / 365)
If it works dont forget to right click cell with formula and "set to column"
Let me know what you think.

Thanks Chris.
It worked w. a small modification, since I did not have a separate column for Today.
I used the following and it worked!
=IF(ISBLANK(DOT@row), (Today()  DOH@row) / 365, (DOT@row  DOH@row) / 365)
Help Article Resources
Categories
Check out the Formula Handbook template!