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

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)






On Mon, 24 Aug 2009 12:49:13 -0700, Maresa
wrote:

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