Determine How Many Hours Fall Within A Range, Using 24 Hour Time

Question

I have one cell (say A1) that will contain a time in 24 hour format.  Let's set the value of A1 to be 17:00.

I have another cell (say B1) that will also contain a time in 24 hour format.  Let's set the value of B1 to be 07:00.

The time in A1 represents the start of a Monday evening work shift for a nurse (meaning the nurse reports to work for 5PM Monday evening).  The time in B2 represents the end of the nursing shift on Tuesday morning (i.e. s/he is done work at 7AM on Tuesday morning).

The nurse gets paid a rate premium for all hours that s/he works between certain hours in a 24 hour period, however, the start time and end time for the premium range varies for different staff.  As such, I use two cells to define the respective premium range.

For this example let's assume the premium time range is from 6PM to 6AM, and so I have entered the premium start time of 18:00 in cell C1, and the premium end time of 06:00 in cell D1. 

I need an excel formula that will look at the respective shift start and end times provided in cells A1 and B1, evaluate them against the premium range defined by the premium start and end times listed in cells C1 and D1, and return the number of hours of premium time the employee is owed (which in this case we know to be 12.00).

**PLEASE NOTE:  I need the results to show as a number with two decimal places, with the decimal places representing the portion of one hour.  For example...

- if the nurse is owed 5 hours and 15 minutes of premium pay, I need the excel formula to return the value of 5.25

- if the nurse is owed 7 hours and 30 minutes of premium pay, I need the excel formula to return the value of 7.50

- if the nurse is owed 3 hours and 45 minutes of premium pay, I need the excel formula to return the value of 3.75

To further clarify, below are two more examples with the desired formula results provided.

Your help with this matter is greatly appreciated.

• Craig

Example 2

---

Cell A1 = 20:30

Cell B1 = 04:00

Cell C1 = 18:00  (same as the original example above)

Cell D1 = 06:00  (same as the original example above)

Excel formula should return a value of 7.50

Example 3

---

Cell A1 = 17:30

Cell B1 = 05:15

Cell C1 = 18:00  (same as the original example above)

Cell D1 = 06:00  (same as the original example above)

Excel formula should return a value of 11.25

Answer 1

Hi Craig,

I think I am near ....

17:00	07:00	18:00	06:00		12		week
20:30	04:00	18:00	06:00		7.5
17:30	05:15	18:00	06:00		11.25
08:00	20:00	06:00	16:00		8
17:00	07:00	20:00	00:00		4		Friday
20:30	04:00	20:00	00:00		3.5
17:30	05:15	20:00	00:00		4
08:00	20:00	20:00	00:00		0
17:00	07:00	00:00	00:00		7		Saturday
20:30	04:00	00:00	00:00		3.5
17:30	05:15	00:00	00:00		6.5
08:00	20:00	00:00	00:00		12
17:00	07:00	00:00	00:00		7		Sunday
20:30	04:00	00:00	00:00		3.5
17:30	05:15	00:00	00:00		6.5
08:00	20:00	00:00	00:00		12
17:00	07:00	00:00	06:00		0		Monday
20:30	04:00	00:00	06:00		0
17:30	05:15	00:00	06:00		0
08:00	20:00	00:00	06:00		0
04:00	08:00	00:00	06:00		2

with following formula:

=MAX((MIN($D1+(D1<=C1),$B1+(B1<=A1))-MAX($C1,$A1))*24,0)

ISO using E, F, ....

I've put it all in C & D

Answer 2

Please use following formula -

=24*(MOD(B1-A1,1)-(B1<A1)*(C1-D1)+MEDIAN(A1,C1,D1)-MEDIAN(B1,C1,D1))

Format your result set as number.

Answer 3

My apologies, but it turns out I need further assistance - I thought the examples I gave would suffice for all my various cases, but it appears not, as I believe I have more issues remaining.

The 1st issue...

Premium hours ranges don't always cross over midnight - they could occur between any time range, on any given day, and may involve only one day, or two.  Let's use the following revised example:

Example 4

---

Cell A1 = 08:00

Cell B1 = 20:00

Cell C1 = 06:00  (same as the original example above)

Cell D1 = 16:00  (same as the original example above)

Excel formula should return a value of 10.00, but in using Vijay's formula I get a value of 4.00

The 2nd issue...

'Weekends' involve special premium ranges, which can run from some time on Friday evening, and last until sometime Monday morning.  As such, I now need to be able to establish a separate set of premium hour ranges to individually define the ranges that apply to the Friday evening portion, all day Saturday, all day Sunday, and some portion of Monday.

The following to clarify:

Let's set Cell E1 at 20:00 to represent the start of the Friday weekend premium range.

Let's set Cell F1 at 24:00 to represent the end of the Friday weekend premium range.

Let's set Cell G1 at 00:00 to represent the start of the Saturday weekend premium range.

Let's set Cell H1 at 24:00 to represent the end of the Saturday weekend premium range.

Let's set Cell I1 at 00:00 to represent the start of the Sunday weekend premium range.

Let's set Cell J1 at 24:00 to represent the end of the Sunday weekend premium range.

Let's set Cell K1 at 00:00 to represent the start of the Monday weekend premium range.

Let's set Cell L1 at 06:00 to represent the end of the Monday weekend premium range.

The problem I'm having here is that when I try to set the value of Cell F1, H1 or J1 at midnight (i.e.  2400), excel automatically changes the value in cell to '00:00'.  How should I deal with this in a manner that will still allow your solution formula to work?

If we can solve the two issues above, I believe I can deal with determining which hours fall into which weekend period, and properly calculate the premium hours.

Thanks once again for your time and effort.

• Craig

Answer 4

My thanks to both Lars-Åke Aspelin and Vijay.  I seem to have working solutions!

Answer 5

I have one cell (say A1) that will contain a time in 24 hour format.  Let's set the value of A1 to be 17:00.

I have another cell (say B1) that will also contain a time in 24 hour format.  Let's set the value of B1 to be 07:00.

The time in A1 represents the start of a Monday evening work shift for a nurse (meaning the nurse reports to work for 5PM Monday evening).  The time in B2 represents the end of the nursing shift on Tuesday morning (i.e. s/he is done work at 7AM on Tuesday morning).

The nurse gets paid a rate premium for all hours that s/he works between certain hours in a 24 hour period, however, the start time and end time for the premium range varies for different staff.  As such, I use two cells to define the respective premium range.

For this example let's assume the premium time range is from 6PM to 6AM, and so I have entered the premium start time of 18:00 in cell C1, and the premium end time of 06:00 in cell D1. 

I need an excel formula that will look at the respective shift start and end times provided in cells A1 and B1, evaluate them against the premium range defined by the premium start and end times listed in cells C1 and D1, and return the number of hours of premium time the employee is owed (which in this case we know to be 12.00).

**PLEASE NOTE:  I need the results to show as a number with two decimal places, with the decimal places representing the portion of one hour.  For example...

- if the nurse is owed 5 hours and 15 minutes of premium pay, I need the excel formula to return the value of 5.25

- if the nurse is owed 7 hours and 30 minutes of premium pay, I need the excel formula to return the value of 7.50

- if the nurse is owed 3 hours and 45 minutes of premium pay, I need the excel formula to return the value of 3.75

To further clarify, below are two more examples with the desired formula results provided.

Your help with this matter is greatly appreciated.

• Craig

Example 2

---

Cell A1 = 20:30

Cell B1 = 04:00

Cell C1 = 18:00  (same as the original example above)

Cell D1 = 06:00  (same as the original example above)

Excel formula should return a value of 7.50

Example 3

---

Cell A1 = 17:30

Cell B1 = 05:15

Cell C1 = 18:00  (same as the original example above)

Cell D1 = 06:00  (same as the original example above)

Excel formula should return a value of 11.25

If we can assume that midnight alway falls within both of the two intervals, try this formula:

=(1-MAX(A1,C1)+MIN(B1,D1))*24

Hope this helps / Lars-Åke