Formula to show duration days in Months, Weeks, and Days
I have a duration in days column and I need to show what that duration is in months, weeks and days. Assuming a month is 30 days and a week is 7 days.
The attached blue columns are what I want to calculate with a formula (manually entered now to show what I want to see).
For example, if something is 38 days, I want it to show 1 month, 1 week, and 1 day.
Hopefully someone can help!
This was the closest post I could find in the community, but they days is not showing accurate for me. https://community.smartsheet.com/discussion/90560/convert-count-of-days-to-months
Best Answer
-
Try something like this...
Months:
=INT([Duration Days]@row / 30)
Weeks:
=INT(([Duration Days]@row - (Months@row * 30)) / 7)
Days:
=[Duration Days]@row - ((Weeks@row * 7) + (Months@row * 30))
Answers
-
Try something like this...
Months:
=INT([Duration Days]@row / 30)
Weeks:
=INT(([Duration Days]@row - (Months@row * 30)) / 7)
Days:
=[Duration Days]@row - ((Weeks@row * 7) + (Months@row * 30))
-
As usual, Paul is a genius.
-
Happy to help. 👍️
-
@Paul Newcome, you're my only hope! I need to take the total number of Time Savings minutes in each row and convert them into days and hours then display that information in the Time Savings Total column. Any ideas?
-
@Laurie Roth What do the numbers in the Time Savings column represent? Are they seconds, minutes, hours, days, etc.?
-
They are minutes! Thank you so much!
-
@Laurie Roth Give this one a try:
=IF(IF(INT([Time Savings]@row / 1440) > 0, INT([Time Savings]@row / 1440) + " Day" + IF(INT([Time Savings]@row / 1440) > 1, "s ", " ")) + IF(([Time Savings]@row - ((INT([Time Savings]@row / 1440)) * 1440)) / 60 > 0, ([Time Savings]@row - ((INT([Time Savings]@row / 1440)) * 1440)) / 60 + " Hour" + IF(([Time Savings]@row - ((INT([Time Savings]@row / 1440)) * 1440)) / 60 <> 1, "s", "")) <> 0, IF(INT([Time Savings]@row / 1440) > 0, INT([Time Savings]@row / 1440) + " Day" + IF(INT([Time Savings]@row / 1440) > 1, "s ", " ")) + IF(([Time Savings]@row - ((INT([Time Savings]@row / 1440)) * 1440)) / 60 > 0, ([Time Savings]@row - ((INT([Time Savings]@row / 1440)) * 1440)) / 60 + " Hour" + IF(([Time Savings]@row - ((INT([Time Savings]@row / 1440)) * 1440)) / 60 <> 1, "s", "")))
If you want to round those hours out to a set decimal (or get rid of the decimals altogether) let me know. We can fit a ROUND function in to take care of that.
-
You are amazing! That worked perfectly! Thank you SOOO much, Paul. (I feel like I just met a celebrity.)
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 464 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!