Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Over time?
Ladies & Gents...Boys and Girls - Cats and Dogs, hope your all well!
I need some assistance please. [Caveat] - My excel skills are rubbish when it comes to mathematics [Caveat /] Based on certain times i work i get Time/time*1.5/time*2 So... Example: Saturday Working from 12:00pm - 23:59 (11:59 Hours) Between 8 and 5 its normal time. Between 17:00:01 - 21:00 its Time*1.5 After 21:00 - 08:00 the following day its Time*2 How can i automate this? Columns: a1 - Date b1 - Day c1- Start Time [hh:mm] d1- End Time [hh:mm] e1 - Break e1 - Total Hours (=SUM(D2-C2)-E2) ? f1 - Overall Time (Automated to include overtime) Too complicated? Is this possible? =IF(AND(OR may be required? Many thanks in Advance.. If you talk bad about country music, it's like saying bad things about my momma. Them's fightin' words. Dolly Parton |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Over time?
OP wrote:
Based on certain times i work i get Time/time*1.5/time*2 [....] Between 8 and 5 its normal time. Between 17:00:01 - 21:00 its Time*1.5 After 21:00 - 08:00 the following day its Time*2 How can i automate this? Columns: a1 - Date b1 - Day c1- Start Time [hh:mm] d1- End Time [hh:mm] e1 - Break e1 - Total Hours (=SUM(D2-C2)-E2) ? f1 - Overall Time (Automated to include overtime) Too complicated? Is this possible? A bit more complicated than necessary. The issues a 1. It would be better if the date were included with the times in C1 and D1, even if you choose to format them as hh:mm. Alternatively, it would be better if work periods were recorded as midnight to 23:59 of the same day. Thus, a swing shift (across midnight) would be recorded as __two__ work periods, one for each day. That might result in two work periods recorded for the same day. 2. I presume "Break" is also in the form h:mm. It would be better if we had "start break" and "end break" times; and even better if they included the date. Otherwise, we are left to guess how to apply break time to regular time, "1.5 time" and "2.0 time". 3. What's the difference between "total hours" and "overall time"? I suspect the latter is intended to be the "time factor" that can be multiplied by hourly wage. And as such, I presume it should be the decimal number of hours, not [h]:mm. 4. You have two columns identified as E1. 5. I assume the above are titles in row 1, and the data starts in row 2. Based on some assumptions.... 1. Total time (F2; format as h:mm): =MAX(0,D2-C2+(D2<=C2)-E2) (D2<=C2) handles the case where we start in one day and finish the next day. MAX(0,...) handles the unlikely case when the break time exceeds the total time (a recording error). 2. Total regular time (G2; format as h:mm): =MAX(0,MIN(D2+(D2<=C2),TIME(17,0,0))-MAX(C2,TIME(8,0,0))) +MAX(0,MIN(D2+(D2<=C2),1+TIME(17,0,0))-MAX(C2,1+TIME(8,0,0))) The second MAX(0,...) handles the case where we work parts of split regular shifts, e.g. 16:00 one day to 16:00 the next day. 3. Total 1.5 time (H2; format as h:mm): =MAX(0,MIN(D2+(D2<=C2),TIME(21,0,0))-MAX(C2,TIME(17,0,0))) +MAX(0,MIN(D2+(D2<=C2),1+TIME(21,0,0))-MAX(C2,1+TIME(17,0,0))) The second MAX(0,...) handles the case where we work parts of split 1.5 time shifts, e.g. 20:00 one day to 20:00 the next day. 4. Total 2.0 time (I2; format as h:mm): =MAX(0,MIN(D2+(D2<=C2),1+TIME(8,0,0))-MAX(C2,TIME(21,0,0))) +MAX(0,MIN(D2+(D2<=C2),TIME(8,0,0))-MAX(C2,0)) The second MAX(0,...) handles the case where we work parts of split 2.0 time shifts, e.g. 7:00 one day to 7:00 the next day. 5. Regular break (J2; format as h:mm): =MIN(E2,G2) 6. 1.5 break (K2; format as h:mm): =MIN(E2-J2,H2) 7. 2.0 break (L2; format as h:mm): =MIN(E2-J2-K2,I2) 8. Time factor (M2; format as Number): =(G2-J2+(H2-K2)*1.5+(I2-L2)*2)*24 9. Total pay (N2): =ROUND(M2*$X$1,2) assuming X1 contains the hourly rate. Note: The break times in #5 through #7 are probably sufficient and reasonable. They assume that breaks are taken first during regular time, then 1.5 time, then 2.0 time. That assumption is probably wrong with the day starts in the evening shift (17:00 and later). Also, the assumption has "odd" results if the work day covers two parts of split regular shifts, for example. All of the break time might be charged as regular time, which a more reasonable assumption might charge the break time to the evening and/or swing shifts. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Over time?
Improvement/errata.... I wrote:
2. Total regular time (G2; format as h:mm): =MAX(0,MIN(D2+(D2<=C2),TIME(17,0,0))-MAX(C2,TIME(8,0,0))) +MAX(0,MIN(D2+(D2<=C2),1+TIME(17,0,0))-MAX(C2,1+TIME(8,0,0))) [....] 3. Total 1.5 time (H2; format as h:mm): =MAX(0,MIN(D2+(D2<=C2),TIME(21,0,0))-MAX(C2,TIME(17,0,0))) +MAX(0,MIN(D2+(D2<=C2),1+TIME(21,0,0))-MAX(C2,1+TIME(17,0,0))) [....] 4. Total 2.0 time (I2; format as h:mm): =MAX(0,MIN(D2+(D2<=C2),1+TIME(8,0,0))-MAX(C2,TIME(21,0,0))) +MAX(0,MIN(D2+(D2<=C2),TIME(8,0,0))-MAX(C2,0)) Simpler, with format correction and correction in #4: 2. Total regular time (G2; format as [h]:mm): =MAX(0,MIN(D2+(D2<=C2),TIME(17,0,0))-MAX(C2,TIME(8,0,0))) + IF(D2<=C2,MAX(0,MIN(D2,TIME(17,0,0))-TIME(8,0,0))) 3. Total 1.5 time (H2; format as [h]:mm): =MAX(0,MIN(D2+(D2<=C2),TIME(21,0,0))-MAX(C2,TIME(17,0,0))) + IF(D2<=C2,MAX(0,MIN(D2,TIME(21,0,0))-TIME(17,0,0))) 4. Total 2.0 time (I2; format as [h]:mm): =MAX(0,MIN(D2+(D2<=C2),1+TIME(8,0,0))-MAX(C2,TIME(21,0,0))) + MAX(0,MIN(D2+(D2<=C2),TIME(8,0,0))-C2) + IF(D2<=C2,MAX(0,D2-TIME(21,0,0))) |
#4
|
|||
|
|||
Hi joeu2004[_2_], Thanks for the time you spent on this, really appreciated
Apologies for vague requirements - excel is not my greatest strength I'm about compile it...i'll let you know how it goes. Thanks Dol |
#5
|
|||
|
|||
ok - its don't seem to be working out the 1.5/2.0 etc.
Answer to Assumption: 1. I agree. Remove the Date and day and the start time includes all: dddd dd/mm/yyyy hh:mm And i will create a new entry when i go past 23.59. 2. I presume "Break" is also in the form h:mm. YES It would be better if we had "start break" and "end break" times; and even better if they included the date. ADDED "start break" and "end break" times; 3. What's the difference between "total hours" and "overall time"? I suspect the latter is intended to be the "time factor" that can be multiplied by hourly wage. Wanted a figure which excluded the 1.5/2.0 calculations and overall time to include 1.5 and 2.0 4. You have two columns identified as E1. - Typo. 5. I assume the above are titles in row 1, and the data starts in row 2. Yes Ive attached - rename to from txt to .xlsx Last edited by Dolly Parton : August 27th 12 at 09:06 PM |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time calculations for Scheduled Time vs. Actual Time Worked | Excel Discussion (Misc queries) | |||
straight time, time and a half, and double time | Excel Discussion (Misc queries) | |||
Calculate Ending time using Start Time and Elapsed Time | Excel Worksheet Functions | |||
Comparing 2 files on date/time stamp, and based time difference do a subroutine | Excel Programming | |||
Calculating days & time left from start date/time to end date/time | Excel Worksheet Functions |