hours into days and hours
Hi All,
How do i change the Duration to days and hours instead of just hours?
Below are the formulas im using
Thanks in advance!
=INT(Sum@row) + " hour(s) " + IF((Sum@row - INT(Sum@row)) * 60 < 10, "0") + (Sum@row - INT(Sum@row)) * 60 + " minutes"
=((VALUE(LEFT([End Time]@row, FIND(":", [End Time]@row) - 1)) + VALUE(RIGHT([End Time]@row, 2)) / 60) + ([End date]@row - [Start Date]@row) * 24) - (VALUE(LEFT([Start Time]@row, FIND(":", [Start Time]@row) - 1)) + VALUE(RIGHT([Start Time]@row, 2)) / 60)
Answers
-
There are a number of time based solutions here that should help get you where you need to be:
-
Thanks Paul and i hate to be that person. I did read through your brilliant post but im struggling to get it to work.
-
Hi @richard_abra,
To adjust your formula to display the duration in days and hours instead of just hours, you can modify the formula to calculate the total duration in hours first, then convert those hours into days and hours. Here's how you can do it, assuming the duration is calculated based on some start and end times/dates:
- First Formula Modification:
- This formula you provided seems to convert a sum of hours (and possibly minutes as a decimal) into a text string showing hours and minutes. To include days, we'll adjust it to calculate days, hours, and minutes.
- The modified formula could look like this:
=INT(Sum@row / 24) + " day(s) " + INT(MOD(Sum@row, 24)) + " hour(s) " + IF((MOD(Sum@row, 1) * 60) < 10, "0", "") + ROUND(MOD(Sum@row, 1) * 60, 0) + " minute(s)"
- Here's what's happening in this formula:
INT(Sum@row / 24)
calculates the total number of days.INT(MOD(Sum@row, 24))
calculates the remaining hours after dividing by 24.IF((MOD(Sum@row, 1) * 60) < 10, "0", "")
checks if the minutes are less than 10 to add a leading zero for formatting.ROUND(MOD(Sum@row, 1) * 60, 0)
calculates the minutes left after removing the hours.
- Second Formula Modification:
- Your second formula calculates the difference between an end time/date and a start time/date to get a duration in hours. To adjust this formula for days and hours:
=INT(((VALUE(LEFT([End Time]@row, FIND(":", [End Time]@row) - 1)) + VALUE(RIGHT([End Time]@row, 2)) / 60) + ([End date]@row - [Start Date]@row) * 24 - (VALUE(LEFT([Start Time]@row, FIND(":", [Start Time]@row) - 1)) + VALUE(RIGHT([Start Time]@row, 2)) / 60)) / 24) + " day(s) " + INT(MOD(((VALUE(LEFT([End Time]@row, FIND(":", [End Time]@row) - 1)) + VALUE(RIGHT([End Time]@row, 2)) / 60) + ([End date]@row - [Start Date]@row) * 24 - (VALUE(LEFT([Start Time]@row, FIND(":", [Start Time]@row) - 1)) + VALUE(RIGHT([Start Time]@row, 2)) / 60)), 24)) + " hour(s)"
- This formula does the following:
- Calculates the total duration in hours between the start and end times/dates.
- Converts the total hours into days and hours, similar to the first formula modification.
Remember, these formulas are designed for Smartsheet's formula syntax. Ensure that your
Sum@row
or any other reference correctly reflects the data you're working with. Adjustments might be needed based on your specific Smartsheet setup and the exact nature of the data (e.g., if "Sum@row" is a placeholder for a more complex calculation or aggregation).bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!