Working with duration data

[PGiessler]

I am working with process duration data, and typically collect it in hour,
minutes and seconds. In some cases, a process step might take over 600 hours
(600:14:30). It seems that as long as I keep everything in a [h]:mm:ss format
(or the 37:30:55 preset) I can sum all the hours from various step whilst
maintaining accuracy. What I was wondering if there is a format that converts
to days, hour, minutes and seconds in the same manner while maintaining the
ability to perform math functions (i.e. addition, subtraction).

Finally, is there way to convert that number to working days, minutes,
hours, and seconds.

Thanks in advance, your answer will make my presentation more boss friendly.

 

[Bernie Deitrick]

PGiessler,

Times are just specially formatted numbers: 600:14:30 is actually the number
25.0100694444444 - the 25 part is full 24 hour days (25*24 = 600) and the
..0100694444444 part is 14minutes and 30 seconds as a fraction of 24 hours.

To all you need to do is change the formatting to show days - but as for
working days - how do you define that? 8 hour days?

HTH,
Bernie
MS Excel MVP

 

[PGiessler]

Hi Bernie,

Thanks for the tips. For my purposes a working day is equal to 10 hours - to
cover time zones of the operation.

PG

PGiessler,

Times are just specially formatted numbers: 600:14:30 is actually the number
25.0100694444444 - the 25 part is full 24 hour days (25*24 = 600) and the
..0100694444444 part is 14minutes and 30 seconds as a fraction of 24 hours.

To all you need to do is change the formatting to show days - but as for
working days - how do you define that? 8 hour days?

HTH,
Bernie
MS Excel MVP

I am working with process duration data, and typically collect it in hour,
minutes and seconds. In some cases, a process step might take over 600
hours
(600:14:30). It seems that as long as I keep everything in a [h]:mm:ss
format
(or the 37:30:55 preset) I can sum all the hours from various step whilst
maintaining accuracy. What I was wondering if there is a format that
converts
to days, hour, minutes and seconds in the same manner while maintaining
the
ability to perform math functions (i.e. addition, subtraction).

Finally, is there way to convert that number to working days, minutes,
hours, and seconds.

Thanks in advance, your answer will make my presentation more boss
friendly.

 

[Bernie Deitrick]

PG,

To calculate the number of 10 hour days, with left over hours and minutes

=INT((A2*24)/10)&" full days and "& INT(A2*24)-INT(A2*24/10)*10&" hour(s) "
&INT(((A2*24)-INT(A2*24))*60)&" minute(s)"

HTH,
Bernie
MS Excel MVP

Hi Bernie,

Thanks for the tips. For my purposes a working day is equal to 10 hours - to
cover time zones of the operation.

PG

PGiessler,

Times are just specially formatted numbers: 600:14:30 is actually the number
25.0100694444444 - the 25 part is full 24 hour days (25*24 = 600) and the
..0100694444444 part is 14minutes and 30 seconds as a fraction of 24 hours.

To all you need to do is change the formatting to show days - but as for
working days - how do you define that? 8 hour days?

HTH,
Bernie
MS Excel MVP

I am working with process duration data, and typically collect it in hour,
minutes and seconds. In some cases, a process step might take over 600
hours
(600:14:30). It seems that as long as I keep everything in a [h]:mm:ss
format
(or the 37:30:55 preset) I can sum all the hours from various step whilst
maintaining accuracy. What I was wondering if there is a format that
converts
to days, hour, minutes and seconds in the same manner while maintaining
the
ability to perform math functions (i.e. addition, subtraction).

Finally, is there way to convert that number to working days, minutes,
hours, and seconds.

Thanks in advance, your answer will make my presentation more boss
friendly.