Calculating hours from Duration colum

Simon Andersson

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? 

 

Shaine Greenwood

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 

Simon Andersson

Will try, Thanks for the help

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.

Craig

 

Shaine Greenwood

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

Thank you.

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

Craig