Excel Help

SilverTongue · 3 Nov 2010 at 23:13

Hi guys,

I'm setting up a spreadsheet to work out my work hours, but trying to base it on my clock card at work. Reason being is that the clock machine went fubar on monday and although payroll have said they'll work it out, I don't trust them as far as I can throw them, so I want to make this sheet so I can give that to them at the end of the week.

Layout is basically

--------------------------------
|Day|In|Out|Total|Running Total|
--------------------------------
|___|__|___|____|___________|

The problem is that when the running total passes 24 hours it start again at 00:00, now I know this is because it is working it out based on a day of 24 hours, but I want it to go up to 40 hours and beyond, is there any way around this?

TIA

Lee · 3 Nov 2010 at 23:53

Try formatting the Running Total column as a "number". That should remove the "time" format.

ie select cells - right mouse click and select "Format Cells..." - on the Number tab choose "Number" (second from top) - choose number of decimal places - click ok - done

Makhaira · 4 Nov 2010 at 07:36

Make sure the formula formatting is as [h].

alfalavala · 4 Nov 2010 at 09:54

I've uploaded an example.

Slightly different to the others' suggestions but that's always the way with excel!

http://www.2shared.com/document/Q1mDzlh2/clocking.html

Looks like:
Day In Out Total (hrs) Running Total (hrs)
01/01/2009 08:00 18:43 10:43 10.72
02/01/2009 07:58 12:43 04:45 15.47

Formula:
Day In Out Total (hrs) Running Total (hrs)
39814 0.333333333333333 0.779861111111111 =C2-B2 =D2*24
39815 0.331944444444444 0.529861111111111 =C3-B3 =E2+D3*24

Formatting:
Day In Out Total (hrs) Running Total (hrs)
dd/mm/yyyy hh:mm hh:mm hh:mm Number

Total is done on hourly basis with minutes as decimal

SilverTongue · 4 Nov 2010 at 23:17

Makhaira said:
Make sure the formula formatting is as [h].

Thanks mate, forgot about the square brackets