COUNTIF Formula to Count Months Between Dates

I am new Smartsheet user and I'm working to convert some Excel files we use over to Smartsheet.

I need to count the number of months an employee has been employed based on their Hire Date but if they have a Term Date the count should stop at that date.

This is the Excel formula I am replacing (I didn't write this):

=IF([Term Date]2>0, (YEAR([Term Date]2)-YEAR([Hire Date]2))*12+MONTH([Term Date]2)-MONTH([Hire Date]2), (YEAR(NOW())-YEAR([Hire Date]2))*12+MONTH(NOW())-MONTH([Hire Date]2))

I know that this Smartsheet formula will give me the number of months from the Hire Date:

=ROUND((NETDAYS([Hire Date]@row, TODAY()) / 365) * 12)

I am unsure of how to stop the count at the Term Date if it exists.

Any help is appreciated!

Find more posts tagged with

Accepted answers

Paul H

@DSSSSALF Try it this way

=ROUND((IF(ISDATE([Term Date]@row), IF(NETDAYS([Term Date]@row, TODAY()) > 0, NETDAYS([Hire Date]@row, [Term Date]@row)), NETDAYS([Hire Date]@row, TODAY())) / 365) * 12)

All comments

Paul H

Try this

If Term Date is a date and its in the past, use hire to term, else use hire to today.

=ROUND((IF(AND(ISDATE([Term Date]@row), NETDAYS([Term Date]@row, TODAY()) > 0), NETDAYS([Hire Date]@row, [Term Date]@row), NETDAYS([Hire Date]@row, TODAY())) / 365) * 12, 0)

LFaraco

https://community.smartsheet.com/discussion/comment/327476#Comment_327476

Thank you! This partially works. It works for employees who have a Term Date but for employees with no Term Date (AKA currently employed) I get an #INVALID DATA TYPE error.

I need to display the number of months employed between Hire Date and Today if there is no Term Date and I think that's the piece missing from the formula:

=ROUND((IF(AND(ISDATE([Term Date]@row), NETDAYS([Term Date]@row, TODAY()) > 0), NETDAYS([Hire Date]@row, [Term Date]@row), NETDAYS([Hire Date]@row, TODAY())) / 365) * 12, 0)

Paul H

@DSSSSALF Try it this way

=ROUND((IF(ISDATE([Term Date]@row), IF(NETDAYS([Term Date]@row, TODAY()) > 0, NETDAYS([Hire Date]@row, [Term Date]@row)), NETDAYS([Hire Date]@row, TODAY())) / 365) * 12)

LFaraco

@Paul H Yes, this works! Thank you so much!

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.