Comparing Ranges of Times

  • Thread starter Thread starter Tomkat
  • Start date Start date
T

Tomkat

Hello,

I am wondering if there is a way to compare to ranges of
times, then compute how many
hours from 1 range fall within the other.
I am trying to calculate hours worked for 2nd shift
differential pay, but there are 5
different scenarios that I can think of. I have
illustrated them as follows....

2nd shift begins at 15:00 and ends at 23:00
A1 = Time in
B1 = Time out


15:00 23:00
| |
1. A1|--+--|B1 |
| |
2. A1|-----------------------|B1
| |
3. | A1|----------|B1 |
| |
4. | A1|---+---|B1
| |
5. A1|--+-----------------------+--|B1
| |
| |

Will I need a separate formula for each scenario...joined
by +IF function,
or is there one formula that will account for all scenarios?

I have been racking my brain for awhile, but I'd rather
save some time by not re-creating
a formula from scratch if there's one already available.

Any help would be GREATLY appreciated!
 
Hi
to get the hours for the second shift use
=(A1<=23/24)*(B1>15/24)*(MIN(B1,23/24)-MAX(A1,15/24))*24

note: works only if your working hours can't span midnight!
 
Wow,
Thanks for the quick reply.
I will need to do the same thing for third shift...which
does span midnight 23:00 --> 7:00 any suggestions?
 
Hi
try
=IF(B1<A1,MIN(B1,7/24)-MAX(A1,23/24)+1,IF(B1>=23/24,B1-MAX(A1,23/24),(A
1<=7/24)*(MIN(B1,7/24)-A1)))
 
Frank,

Thanks again for the responses! With your formulas as a
guide, I was able to work through and create the following
formulas

B8=Time In
C8=Time Out

3RD SHIFT FORMULA
23:00 -> 7:00

IF(C8*24<B8*24,MIN(C8*24,7)-MAX(B8*24,23)+24,IF(C8*24>=23,C8*24-MAX(B8*24,23),(B8*24<=7)*(MIN(C8*24,7)-B8*24)))


2ND SHIFT FORMULA
15:00 -> 23:00

IF(C8*24<B8*24,MIN(C8*24+24,23)-MAX(B8*24,15),IF(C8*24>=15,MIN(C8*24,23)-MAX(B8*24,15),0))

As you can see, I converted the cell values from time to
numbers by multiplying by 24. I found the formulas easier
to understand that way, and then it wasn't necessary to
multiply the end result, which for some reason causes the
formula wizard to stop working.

I've tested every different scenario I can think of, and it
seems to work, but I'd like someone else to take a look at
the formulas and give me an opinion.

The 9 different scenarios I tested are...

07:00 15:00 23:00 07:00
| | | |
1. B8|--+--|C8 | | |
| | | |
2. B8|----------|C8 | |
| | | |
3. | B8|--|C8 | | |
| | | |
4. | B8|--+--|C8 | |
| | | |
5. | B8|----------|C8 |
| | | |
6. | | B8|--|C8 | |
| | | |
7. | | B8|--+--|C8 |
| | | |
8. | | B8|----------|C8
| | | |
9. | | | B8|--|C8 |

Thanks again for all of your help!
 
Back
Top