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).
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,"")
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 | Stop | Hours | 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,"")
