How to Calulculate Hrs and Mins between time frames

C

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)

???
 
R

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)

???
 

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

Top