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

Sherri Watson_DWIHN

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.

Genevieve P.

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")

Genevieve P.

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:

• Article: Create and Edit Formulas in Smartsheet
• Smartsheet Formula Best Practices
• Essential Formulas for Smartsheet Users
• Formulas webinar series

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

Sherri Watson_DWIHN

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.

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.

Sherri Watson_DWIHN

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.

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.

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")

Sherri Watson_DWIHN

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

Appreciate all your help. 🤩

Genevieve P.

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