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
-
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))
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))
-
Kayla ✭✭
As usual, Paul is a genius.
-
Paul Newcome ✭✭✭✭✭✭
-
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 RothWhat 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 RothGive 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 ✭✭✭✭✭✭
Help Article Resources
Categories
I figured it out! Updated formula to get the 2 decimal places as well.<\/p>
=\"We are at \" + IFERROR(ROUND([% closed rate]@row * 100, 2), \"//m.santa-greenland.com/community/discussion/107881/\") + \"% closed rate on ticket status for the month of \" + Month@row<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108861,"type":"question","name":"IF\/AND Formula","excerpt":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I have not received an invoice and the invoice due date is within 30 days. I know I'm close since I've gone from \"unparsable\" to \"incorrect…","snippet":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I…","categoryID":322,"dateInserted":"2023-08-11T16:27:44+00:00","dateUpdated":null,"dateLastComment":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":null,"lastUserID":120231,"lastUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":54,"score":null,"hot":3383549849,"url":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","format":"Rich","lastPost":{"discussionID":108861,"commentID":390268,"name":"Re: IF\/AND Formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390268#Comment_390268","dateInserted":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-11T17:48:48+00:00","dateAnswered":"2023-08-11T17:12:43+00:00","acceptedAnswers":[{"commentID":390261,"body":"
You were, indeed, very close.<\/p>
=IF([Invoice Received?]@row = 0, IF(AND([Renewal Date]@row >= TODAY(), [Renewal Date]@row <= TODAY(+30)), \"Red\"))<\/p>"},{"commentID":390264,"body":"
