Working out Timesheets in Excel

[stengol]

This must have been done before, but i can't find it anywhere.

I want to produce a spreadsheet that works out extra hours worked (either side of 9:00 - 17:30) and the result put in one cell or less hours worked put in another cell.

So far got to this for the extra hours worked cell:

=IF(F8<TIME(9,0,0),IF(G8>TIME(17,30,0),IF((G8-F8)>0,(TIME(9,0,0)-F8)+(G8-TIME(17,30,0)),0)))

Trouble is it only works if the hours are both before and after the normal times. Otherwise I get a return of FALSE.

There needs to be an OR in there somewhere, but that is where i am stuck.

I have put in the IF((G8-F8)>0 part otherwise it thinks people have worked from midnight-midnight.


All help gratefully received.

 

[GlennUK]

Try this ...

=IF(F8<TIME(9,0,0),TIME(9,0,0)-F8,0)+IF(G8>TIME(17,30,0),G8-TIME(17,30,0),0)

Glenn.

 

[stengol]

Great thanks, works a treat apart from solving the problem of thinking you have worked 9 hours extra if you don't put any start and finish times in at all.

 

[GlennUK]

Here's one way to do it ..

=IF(F8=&quot;&quot;,&quot;&quot;,IF(F8<TIME(9,0,0),TIME(9,0,0)-F8,0)+IF(G8>TIME(17,30,0),G8-TIME(17,30,0),0))

which makes the cell blank when there is no start time.

Glenn.