Calculating how many days passed (between 2 dates)

angelapaj
angelapaj ✭✭✭✭
edited 09/14/23 in Formulas and Functions

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.

Best Answer

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer βœ“

    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! πŸ™‚

Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer βœ“

    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
    angelapaj ✭✭✭✭

    @Nick Korna it worked! Thank you ! :)

  • WinaHath
    WinaHath ✭✭✭
    edited 09/28/23

    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
    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!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!