hours into days and hours

richard_abra

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)

Paul Newcome

There are a number of time based solutions here that should help get you where you need to be:

https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time

richard_abra

https://community.smartsheet.com/discussion/comment/418123#Comment_418123

Thanks Paul and i hate to be that person. I did read through your brilliant post but im struggling to get it to work.

Bassam Khalil

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:

1. First Formula Modification:
2. 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.
3. 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)"

1. 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.
2. Second Formula Modification:
3. 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)"

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