Calculate a value between 2 times

M

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
 
C

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top