Time Formats and Multiplication

[mt011]

I am sure this is a simple question, but I will ask nonetheless!

Units Time In Time Out Total Time UPH
500 08:00 13:00 5:00

How can I write a formula to calculate the time then divide the units by total time worked to determine Units Per Hour?

Thanks!!

 

[PaddyD]

On 2002-11-04 17:29, mt011 wrote:
I am sure this is a simple question, but I will ask nonetheless!

Units Time In Time Out Total Time UPH
500 08:00 13:00 5:00

How can I write a formula to calculate the time then divide the units by total time worked to determine Units Per Hour?

Thanks!!

units / (total time*24)

paddy

 

[mt011]

Very simple now that I see the fomula! Thanks a bunch!!

 

[mt011]

OK, I ran in to another problem here. When calculating the total time worked, how do you calculate time that runs past midnight?
C D E F
Units Start Time End Time Time
741 5:00 PM 1:00 AM 8.00

This formula works for anything on the same day:
=SUM(D4*24-E4*24) but not on the above scenario.

Thanks

 

[PaddyD]

you need to read up on the help file re "How excel stores dates & times". see also:

http://www.ozgrid.com/Excel/ExcelDateandTimes.htm

and

http://www.mvps.org/dmcritchie/excel/datetime.htm

for more info.

basically, excel stores times as decimal fractions of dates. Times entered without a date are assumed to have a date value of 0 - hence your propblems. Formulas of the form:

=(A1>B1)+B1-A1

should help.

paddy

 

[mt011]

Thanks for the help! Worked great! I will be sure to check out the links!