Calculate a value between 2 times

[Maresa]

we pay a nightshift allowance between 18:00 and 6:00.
I have a timesheet with start and end times.
Say the shift started at 15:00 and ended at 23:00, I need an answer of 5,
or if the shift started at 04:30 and ended at 12:00 I need an answer of 1.5
thanks

 

[Chip Pearson]

You can use the following formulas. Assume that the start time is in
A1 and the end time is in C1.

To calculate the number of hours between start and 6:00:00, use

=MAX(0,TIME(6,0,0)-A1)*24

To calculate the number of hours between 18:00:00 and end time, use

=MAX(0,C1-TIME(18,0,0)+(C1<TIME(18,0,0)))*24

To calculate the number of hours worked between 6:00:00 and 18:00:00,
excluding hours between 18:00 and 6:00:00, use

=(MIN(C1,TIME(18,0,0))-MAX(A1,TIME(6,0,0))+(C1<A1))*24

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)