Summing Durations Issue

Kea Jolicoeur

Hello ,

I am looking to sum a durations column that has varied input including hours, days and weeks. However it sums the number instead of the indented values. Ex:

1h, 5d, 2w should sum up to*:151, but instead I get 8. Is there a way to convert all durations into hours and then sum? To make it more complicated I am using a report to then filter out hours that are not assigned to my team members, as that is the true sum I want.

Any help much appreciated. The duration column input is variable and will always have hours, days and weeks input, but impossible to predict otherwise I would just use a formula to convert to always convert to hours. Is there a formula that reads the duration value and then converts to hours based on the project settings?

*assuming project setting is set to 5 days a week 10 hours a day

Cheers

-Kea

Bassam Khalil

Hi @Kea Jolicoeur

Hope you are fine, try the following formula ( week = 5 day , day = 5 h )

=VALUE(LEFT([Column2]@row, 1)) + VALUE(MID([Column2]@row, 4, 1)) * 10 + VALUE(MID([Column2]@row, 7, 1)) * 50

if you apply it for cell ( 6h,2d,2w ) you will get ( 126 h ) result.

Kea Jolicoeur

Thanks Bassam, however I am looking to have the formula interpret the value in the cell, so not all cells contain hours, days and weeks, rather the cell alternate in the contents of hours days and week. An example of what i am looking for is below: I have the duration column(input by various users) and i want to use a column formula to yield the hours column(ideally based on the hours in a day via the project setting but i understand that i may have to hard place that in the program pending what i set for each project.

Duration Column Hours Column

20h 20

20h 20

7.8d 78 (assuming project settings says 10 hr days, would be 62.4 with 8hr days)

11d 110

1w 50(assuming 10 hr days, 5 days a week)

Any ideas?