# Formula to show duration days in Months, Weeks, and Days

Options
✭✭✭✭✭
edited 07/20/23

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭
Options

As usual, Paul is a genius.

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

• Options

@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?

• ✭✭✭✭✭✭
Options

@Laurie Roth What do the numbers in the Time Savings column represent? Are they seconds, minutes, hours, days, etc.?

• Options

They are minutes! Thank you so much!

• ✭✭✭✭✭✭
edited 08/09/23
Options

@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.

• Options

You are amazing! That worked perfectly! Thank you SOOO much, Paul. (I feel like I just met a celebrity.)

• ✭✭✭✭✭✭
Options

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!