Calculation of Hrs and Mins from 2 Time Frames
Hi Corey
I should have said you need to format the cells with these formulae as General.
--
Regards
Roger Govier
"Corey" wrote in message ...
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
"Roger Govier" wrote in message ...
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
"Corey" wrote in message ...
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....
|