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
Comments
-
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
-
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
-
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.
-
Thank you.
I'm still having trouble finding a use for DATEONLY() since the change a while back.
Craig
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives