sorry if this is a duplicate, but I cannot see the question I posted last
night.
I need to calculate night shift allowance between the hours 18:00 and 6:00
the shift might run from 15:00 to 23:00, the allowance is then = 5 hours, or
it might start at 05:00 to 14:00, the answer should then be 1 hour.

Assuming start time is in A2, stop time is in B2, formula is:

=MAX(B2,"18:00")-MAX("18:00",A2)+MIN("6:00",B2)-MIN("6:00",A2)

Note this is fairly basic, and won't work for shifts going from one day to
another. As neither of your examples had that occuring, I'm hoping its a safe
assumption.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Maresa" wrote:

sorry if this is a duplicate, but I cannot see the question I posted last
night.
I need to calculate night shift allowance between the hours 18:00 and 6:00
the shift might run from 15:00 to 23:00, the allowance is then = 5 hours, or
it might start at 05:00 to 14:00, the answer should then be 1 hour.

See your post "Calculate a value between 2 times" and the reply of Chip
Pearson.

Wkr,

JP
Calculate a value between 2 times

"Maresa" wrote in message
...
sorry if this is a duplicate, but I cannot see the question I posted last
night.
I need to calculate night shift allowance between the hours 18:00 and 6:00
the shift might run from 15:00 to 23:00, the allowance is then = 5 hours,
or
it might start at 05:00 to 14:00, the answer should then be 1 hour.

Here's the link to OP's other post, for reference:

http://www.microsoft.com/office/comm...f-fb01c927bd9e
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"JP Ronse" wrote:

See your post "Calculate a value between 2 times" and the reply of Chip
Pearson.

Wkr,

JP
Calculate a value between 2 times

"Maresa" wrote in message
...
sorry if this is a duplicate, but I cannot see the question I posted last
night.
I need to calculate night shift allowance between the hours 18:00 and 6:00
the shift might run from 15:00 to 23:00, the allowance is then = 5 hours,
or
it might start at 05:00 to 14:00, the answer should then be 1 hour.

Here is what I posted as a reply yesterday:

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 Tue, 25 Aug 2009 11:51:01 -0700, Maresa
wrote:

sorry if this is a duplicate, but I cannot see the question I posted last
night.
I need to calculate night shift allowance between the hours 18:00 and 6:00
the shift might run from 15:00 to 23:00, the allowance is then = 5 hours, or
it might start at 05:00 to 14:00, the answer should then be 1 hour.

Thanks for the help so far, I did however forgot to mention that shifts might
go from one day to the other eg. starts at 17:00 and ends at 07:00 the next
day where the answer then should be 12 hours, or another scenario where the
shift could start at 05:00 and ends at 23:00 where the answer should be 6
hours

"Luke M" wrote:

Assuming start time is in A2, stop time is in B2, formula is:

=MAX(B2,"18:00")-MAX("18:00",A2)+MIN("6:00",B2)-MIN("6:00",A2)

Note this is fairly basic, and won't work for shifts going from one day to
another. As neither of your examples had that occuring, I'm hoping its a safe
assumption.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Maresa" wrote:

sorry if this is a duplicate, but I cannot see the question I posted last
night.
I need to calculate night shift allowance between the hours 18:00 and 6:00
the shift might run from 15:00 to 23:00, the allowance is then = 5 hours, or
it might start at 05:00 to 14:00, the answer should then be 1 hour.

I think I found a way around it. If the shift goes over midnight, I just type
25:00 for 1 o'clock in the morning, it seems to be working fine then.

Thanks again !

"Maresa" wrote:

Thanks for the help so far, I did however forgot to mention that shifts might
go from one day to the other eg. starts at 17:00 and ends at 07:00 the next
day where the answer then should be 12 hours, or another scenario where the
shift could start at 05:00 and ends at 23:00 where the answer should be 6
hours

"Luke M" wrote:

Assuming start time is in A2, stop time is in B2, formula is:

=MAX(B2,"18:00")-MAX("18:00",A2)+MIN("6:00",B2)-MIN("6:00",A2)

Note this is fairly basic, and won't work for shifts going from one day to
another. As neither of your examples had that occuring, I'm hoping its a safe
assumption.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Maresa" wrote:

sorry if this is a duplicate, but I cannot see the question I posted last
night.
I need to calculate night shift allowance between the hours 18:00 and 6:00
the shift might run from 15:00 to 23:00, the allowance is then = 5 hours, or
it might start at 05:00 to 14:00, the answer should then be 1 hour.