Working with duration data

P

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.
 
B

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
 
P

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

Bernie Deitrick said:
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 said:
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.
 
B

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


PGiessler said:
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

Bernie Deitrick said:
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 said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Display minutes more than 60 2
elapsed time calculation help needed 1
Charts - Time Duration 0
Rewrite a Module 2
Showing time as minutes 8
Problem w/Date time function 2
time data 1
entering time data 2

Top