Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Calculating hours from Duration colum


i would like a formula that can calculate the duration column to Hours in a text column whether i have entered h, days or weeks in the duration column. 

Anyone have succseeded with this? 



  • Hi Simon—

    It might be easier for you to convert the days between your start date and end date using a function like NETWORKDAYS to hours, instead of basing your calculation off of the duration column. The duration column can take any combination of values: hours, days, weeks, etc. so it's harder to parse through.

    Maybe a formula like: =NETWORKDAYS([Start Date]1, [End Date]1) * 24

    You might need to put the DATEONLY function in NETWORKDAYS if you're using dependencies: =NETWORKDAYS(DATEONLY([Start Date]1), DATEONLY([End Date]1)) * 24

    You'll need to tinker with this function and change the cell references to match your sheet. See the help center article on functions for more information: https://help.smartsheet.com/articles/775363-using-formulas#networkdays 

  • Will try, Thanks for the help

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    I'm projecting here, but I don't think Shaine's answer will help. The reason for this is below, but I'll get to the alternative solution first.

    I assume you are looking to convert Duration to Effort, where a 3d task is 24 person-hours (assuming working hours = 8)

    All you need to do is create a new column (I call mine "Effort). The formula is

    =Duration23 * 8

    for row 23. (Again, assuming an 8 hour work day)

    Why it works:

    Duration's underlying value is in days of effort (a number), not the visual aids we need to understand the data better. A one hour task would be shown as 0.125 if they didn't let us use "1h". But the value is still there for us to use.

    Why NETWORKDAYS won't work:

    If you are seeking to convert Duration to Effort, that is a 1 day task takes 8 hours, then using NETWORKDAYS returns misleading or incorrect results.

    If I have an 8 hour task that starts and ends on Monday, then NETWORKDAYS (as used by Shaine above without the *24) will return 1 -- because that is how many work days the task spans. However, if instead the task begins after lunch on Monday and ends before lunch on Tuesday, the function returns 2 -- because that is how many work days the task spans. Multiplying by 8 (instead of 24) would be correct for the first case, but incorrect for the second.

    Also, as an aside, I do not believe DATEONLY does anything for the usage of NETWORKDAYS. Ever.

    Hope that helps.



  • Shaine Greenwood
    Shaine Greenwood Employee
    edited 05/26/17

    This is a great answer; way better than mine.

    I only added the DATEONLY part because of how odd calculations can be when referencing date/time columns. 

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Thank you.

    I'm still having trouble finding a use for DATEONLY() since the change a while back.



This discussion has been closed.