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

Find more posts tagged with

Sheets

Formulas

Community Platform

Accepted answers

Paul Newcome

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

All comments

Paul Newcome

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

Kayla

As usual, Paul is a genius.

Paul Newcome

Happy to help. 👍️

Laurie Roth

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

Paul Newcome

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

Laurie Roth

They are minutes! Thank you so much!

Paul Newcome

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

Laurie Roth

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

Paul Newcome

@Laurie Roth Happy to help. 👍

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.