Spread sheet help needed

wiggyuk

wiggyuk

wiggyuk

Associate
Joined
1 Oct 2004
Posts
707
Location
Rowley Regis, West Mids
Hi all, I am trying to do a spreadsheet to work out staff working hours.

For e.g


Start 12:00
Finish 17:00
this is ok it shows me that they worked 5 hours

Start 17:00
Finish 03:00 the next day
this is wrong shows up -14 hours instead of 10 hours ?

is the a easy way around this ?
 
Assuming start time is in cell A1 and end time is A2:

=IF(A2<=A1,(A2-A1)+1,A2-A1)
Cell format for A1 & A2 is Custom - hh:mm
Cell format for the result is Custom - [h]:mm

Make sense?

EDIT - Beaten to it!
 
Tomsk said:
=IF(start<finish,finish-start,1+finish-start)
Click to expand...

You have lost me ?

chrismox said:
Assuming start time is in cell A1 and end time is A2:

=IF(A2<=A1,(A2-A1)+1,A2-A1)
Cell format for A1 & A2 is Custom - hh:mm
Cell format for the result is Custom - [h]:mm

Make sense?

EDIT - Beaten to it!
Click to expand...

Cheers mate, thats sorted it.
 
Last edited:
Or you could use

=finish+(start>finish)-start

ie =a2+(a1>a2)-a1

pretty much the same as above but the evaluation a1>a2 return either true or false, true is 1, false is 0 so the value of true gets added and as we are working with date formats here 1 day gets added.
 
Last edited:
You have mail.

There are hidden cells that, if you unhide, you'll see what I've done.

I've only changed the top table, not the bottom one.
 
Cheers chrismox, I have had a look and that great but for some reason its not calculating the correct cost now, which should be hours worked * hourly rate - breaks ??
 
You must log in or register to reply here.
Back
Top Bottom