Requesting help with ""Length of Stay" formula as per Excel
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
-
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")
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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.
-
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.
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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?
-
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?
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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")
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thank you so much, THIS IS PERFECT!!!!!
Appreciate all your help. 🤩
-
Wonderful! I'm glad we got there in the end. 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!