Calculating how many days passed (between 2 dates)

Hi All,

I am wondering if you can help.

I have a very basic formula in place to calculate how many days passed from date 1 to date 2. I used NETDAYS formula. Since we use it to calculate the time from when the issue was raised until it was resolved - the end date is not always there straightaway which of course brings back #INVALID DATA TYPE error.

Is there a way for the column to stay clear until the end date is provided so the calculation can be done and the correct number can appear in the column? The sheet is to be shared with top management and I'm trying to make it as clear as possible.

I'd appreciate all the suggestions.

Find more posts tagged with

Accepted answers

Nick Korna

Hi @angelapaj,

You can do this with an IFERROR statement:

=IFERROR(NETDAYS([Date1]@row, [Date2]@row), "")

If you don't have a value for Date2, then the column will be blank, otherwise the NETDAYS calculation will be done as normal.

Hope this helps, any problems/questions then just post! 🙂

All comments

Nick Korna

Hi @angelapaj,

You can do this with an IFERROR statement:

=IFERROR(NETDAYS([Date1]@row, [Date2]@row), "")

If you don't have a value for Date2, then the column will be blank, otherwise the NETDAYS calculation will be done as normal.

Hope this helps, any problems/questions then just post! 🙂

angelapaj

@Nick Korna it worked! Thank you ! :)

WinaHath

what about calculating how many YEARS between dates? I have over 800 contracts that I want to just automatically calculate the contract term.

Or Start Date + 3 years and output End date

Nick Korna

Hi @WinaHath

For years between dates:

=IFERROR((NETDAYS([Date1]@row, [Date2]@row)/365), "")

This will give you a decimal, but you can add ROUND or similar if you just want the closest amount of years.

For Start Date +3 years I'd use:

=DATE(YEAR([Start Date]@row) + 3, MONTH([Start Date]@row), DAY([Start Date]@row))

In the event of a leap year date being picked (e.g. 29/02/24), the 3 years on date will end up on 1st of March in the third year (e.g. 01/03/27).

Hope this helps!

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.