Calculation of Hrs and Mins from 2 Time Frames

C

Corey

Example:
Normal Hours= 7:30am - 3:30pm MON-FRI.
Time & Half Hours= 3:30pm-6:30pm MON-FRI and First 3hours on a SAT.
Double Time= All other hours remaining.

Senario:
A1=29/5/2006 7:30am
B1=29/5/2006 8:00pm

C1=8 (Normal Hours)
D1=3 (Time & Half Hours)
E1=1.5 (Double Hours)


A1=[Date &Time] START
B1=[Date & Time] FINISH

C1=CALC Value()
D1=CALC Value()
E1=CALC Value()

How would i set this up so that i simply put in the DAT & TIME of A1 and B1, and the hours are placed in their relative category?

Is this a VBA solution, or a Formula solution????

Corey....
 
R

Roger Govier

Hi Corey

I posted a solution to this in .Excel 4 hours ago.
It helps if you stick with your original thread, rather than creating new threads in new groups.
In case you missed it, I repeat the solution I posted below.

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


Example:
Normal Hours= 7:30am - 3:30pm MON-FRI.
Time & Half Hours= 3:30pm-6:30pm MON-FRI and First 3hours on a SAT.
Double Time= All other hours remaining.

Senario:
A1=29/5/2006 7:30am
B1=29/5/2006 8:00pm

C1=8 (Normal Hours)
D1=3 (Time & Half Hours)
E1=1.5 (Double Hours)


A1=[Date &Time] START
B1=[Date & Time] FINISH

C1=CALC Value()
D1=CALC Value()
E1=CALC Value()

How would i set this up so that i simply put in the DAT & TIME of A1 and B1, and the hours are placed in their relative category?

Is this a VBA solution, or a Formula solution????

Corey....
 
C

Corey

Thanks.
I did post again, as i only subscribed to this when i got home from work.
I did search for my other post but could not find it, to see if someone replied.

I had to adapt the cell alues in the formula, and it works for the NORM and Time/Half but i get
1/8/00 12:00 AM

for the E1 cell??
Any ideas

Hi Corey

I posted a solution to this in .Excel 4 hours ago.
It helps if you stick with your original thread, rather than creating new threads in new groups.
In case you missed it, I repeat the solution I posted below.

Try
C1 =MIN(8,MOD(B1-A1,1)*24)
D1 =MIN(3,MAX(0,MOD(B1-A1,1)*24-C1))
E1 =MOD(B1-A1,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


Example:
Normal Hours= 7:30am - 3:30pm MON-FRI.
Time & Half Hours= 3:30pm-6:30pm MON-FRI and First 3hours on a SAT.
Double Time= All other hours remaining.

Senario:
A1=29/5/2006 7:30am
B1=29/5/2006 8:00pm

C1=8 (Normal Hours)
D1=3 (Time & Half Hours)
E1=1.5 (Double Hours)


A1=[Date &Time] START
B1=[Date & Time] FINISH

C1=CALC Value()
D1=CALC Value()
E1=CALC Value()

How would i set this up so that i simply put in the DAT & TIME of A1 and B1, and the hours are placed in their relative category?

Is this a VBA solution, or a Formula solution????

Corey....
 
M

Marcelo

Hi Corey,

I solved it as follow,

On A1 put 07:30
On B1 put 15:30
on C2 put 18:30

imagine that you have on A6 29/5/2006 07:30 and on B6 29/5/2006 20:00

Formula on C6 (normal hours)
=if(or(weekday(a6)=1,weekday(a6)=7),if(and(time(hour(a6),minute(a6),0)=a1,time(hour(b6),minute(b6),0)-time(hour(a6),minute(a6),0)>(b1-a1),(b1-a1),time(hour(b6),minute(b6),0)-time(hour(a6),minute(a6),0)))

Formula on D6 (T&H hours)
=if(weekday(a6)=1,0,if(time(hour(b6),minute(b6),0)-time(hour(a6),minute(a6),0)-c6>(c1-b1),(c1-b1)time(hour(b6),minute(b6),0)-time(hour(a6),minute(a6),0)-c6))

Formula on E6 (double)
=if(time(hour(b6),minute(b6),0)-time(hour(a6),minute(a6),0)>soma(c6:d6),time(hour(b6),minute(b6),0)-time(hour(a6),minute(a6),0)-soma(c6:d6),0)

hope its helps
Regards from Brazil
Marcelo











"Corey" escreveu:
 
C

Corey

I get a formula error with what you posted when i try to input it into a
cell

Marcelo said:
Hi Corey,

I solved it as follow,

On A1 put 07:30
On B1 put 15:30
on C2 put 18:30

imagine that you have on A6 29/5/2006 07:30 and on B6 29/5/2006 20:00

Formula on C6 (normal hours)
=if(or(weekday(a6)=1,weekday(a6)=7),if(and(time(hour(a6),minute(a6),0)=a1,time(hour(b6),minute(b6),0)-time(hour(a6),minute(a6),0)>(b1-a1),(b1-a1),time(hour(b6),minute(b6),0)-time(hour(a6),minute(a6),0)))

Formula on D6 (T&H hours)
=if(weekday(a6)=1,0,if(time(hour(b6),minute(b6),0)-time(hour(a6),minute(a6),0)-c6>(c1-b1),(c1-b1)time(hour(b6),minute(b6),0)-time(hour(a6),minute(a6),0)-c6))

Formula on E6 (double)
=if(time(hour(b6),minute(b6),0)-time(hour(a6),minute(a6),0)>soma(c6:d6),time(hour(b6),minute(b6),0)-time(hour(a6),minute(a6),0)-soma(c6:d6),0)

hope its helps
Regards from Brazil
Marcelo











"Corey" escreveu:
Example:
Normal Hours= 7:30am - 3:30pm MON-FRI.
Time & Half Hours= 3:30pm-6:30pm MON-FRI and First 3hours on a SAT.
Double Time= All other hours remaining.

Senario:
A1=29/5/2006 7:30am
B1=29/5/2006 8:00pm

C1=8 (Normal Hours)
D1=3 (Time & Half Hours)
E1=1.5 (Double Hours)


A1=[Date &Time] START
B1=[Date & Time] FINISH

C1=CALC Value()
D1=CALC Value()
E1=CALC Value()

How would i set this up so that i simply put in the DAT & TIME of A1 and
B1, and the hours are placed in their relative category?

Is this a VBA solution, or a Formula solution????

Corey...
Click to expand...
Click to expand...
 
R

Roger Govier

Hi Corey

I should have said you need to format the cells with these formulae as General.

--
Regards

Roger Govier


Thanks.
I did post again, as i only subscribed to this when i got home from work.
I did search for my other post but could not find it, to see if someone replied.

I had to adapt the cell alues in the formula, and it works for the NORM and Time/Half but i get
1/8/00 12:00 AM

for the E1 cell??
Any ideas

Hi Corey

I posted a solution to this in .Excel 4 hours ago.
It helps if you stick with your original thread, rather than creating new threads in new groups.
In case you missed it, I repeat the solution I posted below.

Try
C1 =MIN(8,MOD(B1-A1,1)*24)
D1 =MIN(3,MAX(0,MOD(B1-A1,1)*24-C1))
E1 =MOD(B1-A1,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


Example:
Normal Hours= 7:30am - 3:30pm MON-FRI.
Time & Half Hours= 3:30pm-6:30pm MON-FRI and First 3hours on a SAT.
Double Time= All other hours remaining.

Senario:
A1=29/5/2006 7:30am
B1=29/5/2006 8:00pm

C1=8 (Normal Hours)
D1=3 (Time & Half Hours)
E1=1.5 (Double Hours)


A1=[Date &Time] START
B1=[Date & Time] FINISH

C1=CALC Value()
D1=CALC Value()
E1=CALC Value()

How would i set this up so that i simply put in the DAT & TIME of A1 and B1, and the hours are placed in their relative category?

Is this a VBA solution, or a Formula solution????

Corey....
 
M

Marcelo

Corey, sorry my bad

For the normal hour
=if(or(weekday(a6)=1,weekday(a6)=7),0,if(and((time(hour(a6),minute(a6),0)=a1,((time(hour(b6),minute(b6),0)-time(hour(a6),minute(a6),0))>(b1-a1))),(b1-a1),(time(hour(b6),minute(b6),0)-time(hour(a6),minute(a6),0))))

For T&H hou
=if(weekday(A6)=1,0,if((time(hour(B6),minute(B6),0)>C1),(C1-B1),(time(hour(B6),minute(B6),0)-B1)))

For the doubl
IF((TIME(HOUR(B6),MINUTE(B6),0)-TIME(HOUR(A6),MINUTE(A6),0))>SUN(C6:D6),(TIME(HOUR(B6),MINUTE(B6),0)-TIME(HOUR(A6),MINUTE(A6),0))-SUN(C6:D6),0)

I aprreciate you rfeedback
regards from Brazil
Marcelo


"Corey" escreveu:
I get a formula error with what you posted when i try to input it into a
cell

Marcelo said:
Hi Corey,

I solved it as follow,

On A1 put 07:30
On B1 put 15:30
on C2 put 18:30

imagine that you have on A6 29/5/2006 07:30 and on B6 29/5/2006 20:00

Formula on C6 (normal hours)
=if(or(weekday(a6)=1,weekday(a6)=7),if(and(time(hour(a6),minute(a6),0)=a1,time(hour(b6),minute(b6),0)-time(hour(a6),minute(a6),0)>(b1-a1),(b1-a1),time(hour(b6),minute(b6),0)-time(hour(a6),minute(a6),0)))

Formula on D6 (T&H hours)
=if(weekday(a6)=1,0,if(time(hour(b6),minute(b6),0)-time(hour(a6),minute(a6),0)-c6>(c1-b1),(c1-b1)time(hour(b6),minute(b6),0)-time(hour(a6),minute(a6),0)-c6))

Formula on E6 (double)
=if(time(hour(b6),minute(b6),0)-time(hour(a6),minute(a6),0)>soma(c6:d6),time(hour(b6),minute(b6),0)-time(hour(a6),minute(a6),0)-soma(c6:d6),0)

hope its helps
Regards from Brazil
Marcelo











"Corey" escreveu:
Example:
Normal Hours= 7:30am - 3:30pm MON-FRI.
Time & Half Hours= 3:30pm-6:30pm MON-FRI and First 3hours on a SAT.
Double Time= All other hours remaining.

Senario:
A1=29/5/2006 7:30am
B1=29/5/2006 8:00pm

C1=8 (Normal Hours)
D1=3 (Time & Half Hours)
E1=1.5 (Double Hours)


A1=[Date &Time] START
B1=[Date & Time] FINISH

C1=CALC Value()
D1=CALC Value()
E1=CALC Value()

How would i set this up so that i simply put in the DAT & TIME of A1 and
B1, and the hours are placed in their relative category?

Is this a VBA solution, or a Formula solution????

Corey...
Click to expand...
Click to expand...
Click to expand...
 

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

Over time? 4
How to Calulculate Hrs and Mins between time frames 2
Conditional Time Calculation 10
Countif between Time stamps and have - value 0
using if statement to subtract 24 hours from time 2
Help with If statement to subtract time (follow up) 5
If Hours >0, If Volume <= 0,(and vice-versa) then.... 4
Time Calculation 5

Top