Timesheet number of hours

snottus

New member
Joined
Aug 21, 2014
Messages
2
Reaction score
0
Points
0
Hey,


Apologies in advance if this has already been asked - but here's the deal.
I'm using Google docs to make a handy time-sheet for myself. I've figured out how to make it sum up the amount of hours between start and stop times, but the following has proven more complex. I've tried googling quite a bit, but either I'm bad at it, or the solution is more elusive than I had imagined. So:

Say I've worked from 14:00 to 23:00. That is 9 hours of work - easy.
Now, from 17:00 to 22:00, I get an evening bonus to my wages. From 22:00 to 01:00 there's the night bonus. (there's also a late night bonus for 01:00 to 06:00).

What I was hoping you guys could help me with, is make a formula that registers whether or not I've worked any hours that has bonuses, and if so, how many. (Working 14-23, I get 5 hours evening bonus (17-22) and 1 hour night bonus (22-01).

A1
B1
C1
D1
E1
Start
StopHours
Evening
Night
14:00
23:00
9
5
1

I'm using the following formula to calculate the number of hours:
=If($A1>0,Hour($B1-$A1+($B1<$A1))+Minute($B1-$A1+($B1<$A1))/60,"")
 

snottus

New member
Joined
Aug 21, 2014
Messages
2
Reaction score
0
Points
0
Answer and solution was supplied over at Excel Forum, please disregard this thread, as it is now obsolete.
 
Top