Military Time Formula Needed

Creating spreadsheet which can figure out total hours worked. Time clock uses Military/24hr time, so want to just type in start and end time and get total hours worked.

Hi BubbasExcel

Covering the case of start time is before midnight and end time afterwards makes it more challenging.

A1 21:30 and B1 06:45

Hours:
=(B1-A1+(B1<A1))*24
Returns: 9.25
Minutes:
=(B1-A1+(B1<A1))*(24*60)
Returns: 555
Seconds:
=(B1-A1+(B1<A1))*(24*60*60)
Returns: 33300

The structure (B1<A1) in this context can be described as an implicit IF function which returns 1 if True and 0 if False. Since time is measured as a decimal of a day, the addition of 1 serves to calculate the difference between 1 + B1 and A1 which is what you want. In the case of minutes and seconds calculations the conversion from decimal part of a day has not been simplified to 1440 and 86400 as this makes understanding of the logic more easy.

But a lot depends on how your data is entered.

Hope this helps more than it confuses.

Norman Harker

Thanks Norman,
We don't have a third shift so it would only be am to pm. I'll give it a shot.
Thanks,
Dan

Hi BubbasExcel!

In that case you can use:

A1 09:30 and B1 14:45

=(B1-A1)*24
Returns 5.25

The trouble here is that it works OK until someone sets up a night shift! By that time you're "outahere"!

Most important though is how those times are entered. I've used Excel's intellisence that translates 06:30 as a time requirement.

You import data using a different system or enter differently and you are going to have to parse the data to get it to Excel's time system.

Hope this helps more than it confuses.

Norman Harker

Need a spreadsheet that would account for military time.

Example:


A(IN) B(OUT) TOTAL MINS

0800 2300 900
0100 2045 1185
1830 130 300


(1) Need to know how to format cells to show military time
(2) Need formula to account for next day... (3rd example)

I have the following formula

=ABS(TIME(LEFT(A1,2),RIGHT(A1,2),0)-TIME(LEFT(B1,2),RIGHT(B1,2),0))*1440.... maybe I just need item one.

Thanks