#### 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

Options
edited 12/09/19

Hi,

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?

• Employee
Options

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

• Options

Will try, Thanks for the help

• ✭✭✭✭✭✭
Options

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.

Craig

• Employee
edited 05/26/17
Options

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.

• ✭✭✭✭✭✭
Options

Thank you.

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

Craig

This discussion has been closed.