Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculation of Hrs and Mins from 2 Time Frames
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.... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculation of Hrs and Mins from 2 Time Frames
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 "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.... |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculation of Hrs and Mins from 2 Time Frames
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.... |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculation of Hrs and Mins from 2 Time Frames
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(ho ur(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... |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculation of Hrs and Mins from 2 Time Frames
I get a formula error with what you posted when i try to input it into a
cell "Marcelo" wrote in message ... 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(ho ur(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... |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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.... |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculation of Hrs and Mins from 2 Time Frames
Corey, sorry my bad
For the normal hours =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 hour =if(weekday(A6)=1,0,if((time(hour(B6),minute(B6),0 )C1),(C1-B1),(time(hour(B6),minute(B6),0)-B1))) For the double 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" wrote in message ... 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(ho ur(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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|