IF Function

Bert Hoffbeck

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.

Just_Chris

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.

Just_Chris

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.

Bert Hoffbeck

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)