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.

Find more posts tagged with

Formulas

Accepted answers

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.

All comments

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)

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.