Requesting help with ""Length of Stay" formula as per Excel

Options

I am receiving the #UNPARSEABLE message for the following formula to reflect length of stay between two entered dates using =IFDATED([MOVE-IN DATE]1,[ASSESSMENT DATE]1,"Y") & " Yrs, " & DATEDIF([MOVE-IN DATE]1,[ASSESSMENT DATE]1,"YM") & " Mnths, " & DATEDIF([MOVE-IN DATE]1,[ASSESSMENT DATE]1,"MD") & " Days"


Please assist.

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @Sherri Watson_DWIHN

    Try this formula, to include both Months and Days. I've based this off of a 30-day month, so you may see slight inconsistencies within the "day" portion depending on what months your dates are spanning:

    =ROUNDDOWN(NETDAYS([Start Date]@row, [End Date]@row) / 365) + " Yrs " + (ROUNDDOWN(([End Date]@row - [Start Date]@row) / 30) - (ROUNDDOWN((NETDAYS([Start Date]@row, [End Date]@row) / 365)) * 12) + " Months ") + (NETDAYS([Start Date]@row, [End Date]@row) - (ROUNDDOWN((NETDAYS([Start Date]@row, [End Date]@row) / 365)) * 365) - ((ROUNDDOWN(([End Date]@row - [Start Date]@row) / 30) - (ROUNDDOWN((NETDAYS([Start Date]@row, [End Date]@row) / 365)) * 12)) * 30) + " Days")

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Sherri Watson_DWIHN

    Excel and Smartsheet have different formulas and functions. Would you be able to write out what you're looking to achieve in words?

    Smartsheet doesn't have a function called IFDATED, so I'm guessing you're looking to see how many days are between dates, is that correct?

    You can simply subtract one date from another to receive a number of days:

    [ASSESSMENT DATE]@row - [MOVE-IN DATE]@row

    See: Use Formulas to Perform Calculations With Dates

    Or if you're looking to see working days then you'll want to use the NETWORKDAYS Function:

    NETWORKDAYS([MOVE-IN DATE]@row, [ASSESSMENT DATE]@row)


    Then if you're wanting a certain text value to appear next to the number you can add it to the formula with + like so:

    = [ASSESSMENT DATE]@row - [MOVE-IN DATE]@row + "Text"


    Here are some resources that may be useful to you as you build formulas in Smartsheet:

    If this hasn't helped, it would be useful to see a screen capture of your Smartsheet sheet with the description of your end-goal, but please block out sensitive data.

    Cheers,

    Genevieve

  • Thank you: the formaula suggestion is indeed a GREAT helpful. I am attempting to utilize an excel formaula that gives Length of Stay between two dates showing the totalitly in years, months, and days. If this is not possible, then I believe when can come up with a different solution.


    Please advise.

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Sherri Watson_DWIHN

    Do you want the Year, Month, and Day calculations to be separate?

    So if something took 14 months, would you want it to say

    1 Year / 14 months / 426 days

    or do you want to break that down, so the total is shown all together:

    1 Year, 2 months, 5 days


    The first option would be fairly straight forward:

    • Total Year Formula:

    =YEAR([End Date]@row) - YEAR([Start Date]@row) + " Yrs"

    • Total Month Formula:

    =ROUND((NETDAYS([Start Date]@row, [End Date]@row) / 365 * 12)) + " Months"

    • Total Day Formula:

    =[End Date]@row - [Start Date]@row


    These would each give you a separate calculation for how many Years or Months or Days there are between two dates.

  • Oh, wow! It definietly would be the second options: the breakdown to show the total all together (your second example: 1 Year, 2 months, 5 days)

    Is that formaula listed here?

  • Genevieve P.
    Genevieve P. Employee Admin
    edited 03/29/22
    Options

    Thanks for clarifying, @Sherri Watson_DWIHN

    This gets a little trickier; the Year is fairly straight forward, but the Months and Days get tricky.

    =ROUNDDOWN(NETDAYS([Start Date]@row, [End Date]@row) / 365) + " Yrs "

    The MONTH function can look at a Date and return the Month number. This means we could subtract the End Date Month from the Start Date month, but if you're in different years, then subtracting 1 (Jan) from 11 (Nov) would give you the wrong number.

    We can use an IF statement to say IF the Month number is greater, then subtract the Start month, but if it's less, subtract the End month number.

    For example:

    IF(MONTH([End Date]@row) > MONTH([Start Date]@row), MONTH([End Date]@row) - MONTH([Start Date]@row), MONTH([Start Date]@row) - MONTH([End Date]@row)) + " Months "


    But this is only looking at the month number, not the days. This means if you have a Start Date of Jan 25th, and an End Date of Feb 2nd, it will read "1 month" even though it's actually just a few days.


    I would personally suggest JUST doing the Year and Days, skipping the Months all together:

    =ROUNDDOWN(NETDAYS([Start Date]@row, [End Date]@row) / 365) + " Yrs " + (ROUNDDOWN(([End Date]@row - [Start Date]@row) / 30) - (ROUNDDOWN((NETDAYS([Start Date]@row, [End Date]@row) / 365)) * 12) + " Months ")

    This will find out how many years are listed, multiply this by 365 and subtract it from the DAY count. Is this good enough or do you need the Month?

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @Sherri Watson_DWIHN

    Try this formula, to include both Months and Days. I've based this off of a 30-day month, so you may see slight inconsistencies within the "day" portion depending on what months your dates are spanning:

    =ROUNDDOWN(NETDAYS([Start Date]@row, [End Date]@row) / 365) + " Yrs " + (ROUNDDOWN(([End Date]@row - [Start Date]@row) / 30) - (ROUNDDOWN((NETDAYS([Start Date]@row, [End Date]@row) / 365)) * 12) + " Months ") + (NETDAYS([Start Date]@row, [End Date]@row) - (ROUNDDOWN((NETDAYS([Start Date]@row, [End Date]@row) / 365)) * 365) - ((ROUNDDOWN(([End Date]@row - [Start Date]@row) / 30) - (ROUNDDOWN((NETDAYS([Start Date]@row, [End Date]@row) / 365)) * 12)) * 30) + " Days")

  • Thank you so much, THIS IS PERFECT!!!!!

    Appreciate all your help. 🤩

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Wonderful! I'm glad we got there in the end. 🙂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!