How to Calulculate Hrs and Mins between time frames

[Corey]

If i have 2 cells with time values say: A1=7:30AM and B1=3:30PM.

7:30AM - 3:30PM set to NORMAL Hours

3:30PM - 6:30PM set to TIME & HALF Hours

Any time after 6:30PM - 7:29AM to be DOUBLE TIME hours

Then if i set 4 other cells for data input:
A2=( DATE 1) 29/5/2009 Start date. B2=(Date 2) 29/5/2006 Finish date.
A3=(Time) 7:30AM Start Time. B2= (Time) 7:00PM Finish Time.

How can i set 3 Cells say (C1:E1) to display from the above example :
C1=8 hrs (NORMAL TIME)
D1=3hrs (Time & HALF)
E1=2hrs (DOUBLE)

???

 

[Dav]

If your time frames can not last more than 24 hours it becomes much more
straightforward. Hopefully this thread is a good starting point, as once
you have got times to shifts you just multiply them by the appropriate
amount!

http://www.excelforum.com/showthread.php?t=514668&highlight=shifts

Regards

Dav

 

[Roger Govier]

Hi Corey

Try
C1 =MIN(8,MOD(B3-A3,1)*24)
D1 =MIN(3,MAX(0,MOD(B3-A3,1)*24-C1))
E1 =MOD(B3-A3,1)*24-C1-D1

The MOD() part of the formula is there to deal with any situations where the end time is on a different day to the start time.
The *24 is to deal with the fact that Excel stores times as fractions of a day (24 hours)


--
Regards

Roger Govier


If i have 2 cells with time values say: A1=7:30AM and B1=3:30PM.

7:30AM - 3:30PM set to NORMAL Hours

3:30PM - 6:30PM set to TIME & HALF Hours

Any time after 6:30PM - 7:29AM to be DOUBLE TIME hours

Then if i set 4 other cells for data input:
A2=( DATE 1) 29/5/2009 Start date. B2=(Date 2) 29/5/2006 Finish date.
A3=(Time) 7:30AM Start Time. B2= (Time) 7:00PM Finish Time.

How can i set 3 Cells say (C1:E1) to display from the above example :
C1=8 hrs (NORMAL TIME)
D1=3hrs (Time & HALF)
E1=2hrs (DOUBLE)

???