Hours between specific times

**colins5286** · 03-31-2012, 08:10 AM

Hello,

I am trying to create a formula to work out the number of hours between specific times. This is to calculate a shift allowance payment.

Any hours worked between 20:00 and 06:00 will be enhanced by a shift allowance. I need a spreadsheet where the user enters the start and end of their shift, the formula should then work out if this shift qualifies for the allowance and calculate the number of hours to be enhanced:

e.g.

Start: 31/03/2012 22:00
End: 01/04/2012 07:00
Total hours: 9
Enhanced: 8

I haven't used excel for a long time and just cant begin to think how you would do this.
Thanks

**daddylonglegs** · 03-31-2012, 09:05 AM

If you have start time/date in A2 and end time/date in B2 then you can get total hours with this formula

=(B2-A2)*24

....and then hours between 20:00 and 06:00 only with this one

=(B2-A2)*10+(MOD(B2,1)-MOD(A2,1))*14-MEDIAN(MOD(B2,1)*24,6,20)+MEDIAN(MOD(A2,1)*24,6,20)

**colins5286** · 03-31-2012, 10:20 AM

Thanks for that, it works brilliantly.
If ever the allowance hours were to change say 21:00 - 06:00, which part of the formula would I amend?
Many thanks for your help.

**daddylonglegs** · 03-31-2012, 10:57 AM

You'd need to change the 20s and 6s and also 14 and 10 (10 being the number of hours between 20:00 and 06:00), so for 21:00 to 06:00 change to

=(B2-A2)*9+(MOD(B2,1)-MOD(A2,1))*15-MEDIAN(MOD(B2,1)*24,6,21)+MEDIAN(MOD(A2,1)*24,6,21)