Timesheet - Non Military Time

[Abraxus]

My wife just started a new job (yeah!) and they have her using an Excel time sheet.

She asked me if I could help her get it to total the times, but I'm having a bit of trouble.

The columns she fills out are:
In, Out, In, Out

There are 2 Sub-totals. One for the first set of In/Out and one for the second set.

There is then a Total, the sum of the 2 sub totals.

All in all, it looks like this:
In Out SubTotal1 In Out SubTotal2 Total

Now, I could do this easily if I could get her to do military time, but no dice.

How can I set it up so that the SubTotals and Total work if she doesn't use military time?

An example of a shift she worked is:
In 11:00
Out 2:00
In 2:30
Out 6:00

That makes SubTotal1 = 3 and SubTotal2 = 3.5 with a total of 6.5.

If it helps, the store is only open 8-6, so she never crosses midnight.

Any help is greatly appreciated!

[HansV]

Hi Morgan, welcome to Eileen's Lounge!

The main problem with adding times in Excel is that the default time format uses clock time, not accumulated time. So if the total time is 26 hours and 30 minutes, it'd be displayed as 2:30.

To get around this, you can use a custom format [h]:mm. The square brackets around the h tell Excel that you want to display accumulated time. 26 hours and 30 minutes will be displayed as 26:30.

If you'd rather display the number of hours as a decimal number - in the above example 26.5 instead of 26:30, you can multiply the total by 24 and format it as a number with the desired number of decimal places. So if the original formula looks like =B20+D20, change it to =24*(B20+D20) and use Format | Cells... to format the cell with the formula as a number.