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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
  • 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
    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).

    PMP Certified

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!