| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 79888 | 2007-06-04 23:51:00 | Excel time function | shivmadan (12364) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 556126 | 2007-06-04 23:51:00 | Can someone please help me with the following function in Excel. I want to make employees' schedule. I want 2 cells at the bottom of the schedule to tell me how many hours employees workd before 4:00 PM and how how many hours the employees worked after 4:00 PM. Let's say the employees start working at 7:00 AM and work until 11:00 PM. I would be interested in knowing how many hours were put in before 4:00 PM and how many hours after. Someone please help with the formula Thank you Shiv |
shivmadan (12364) | ||
| 556127 | 2007-06-05 03:48:00 | Let's say the employees start working at 7:00 AM and work until 11:00 PM . Let them go home, thats way too long to be stuck at work . |
pctek (84) | ||
| 556128 | 2007-06-05 04:52:00 | for the example you gave the calculation is as follows: Start: 7:00 Finish: 23:00 Middle Time: 16:00 First find the difference between 4 pm and 11 pm with =sum(Finish-Middle Time) this is the time after 4pm then calculate calculate =sum((Finish-Start)-Middle Time) to find the time before 4 pm =sum(Finish-Middle Time) = 7:00 hrs =sum((Finish-Start)-Middle Time) =9:00 hrs |
spaceman8815 (4112) | ||
| 556129 | 2007-06-05 05:02:00 | And when you start a night shift ... ? If someone starts at 11 p.m., and finishes at 7 a.m. is this time before or after 4 p.m.? | Graham L (2) | ||
| 556130 | 2007-06-05 08:47:00 | Try this. I’m assuming that employee gets a lunch break! Format columns A and B for 24hour times e.g. 07:00 In A1 enter 16:00 which is the reference time you are checking against Enter hours worked in b1, b2 and b3, b4 i.e. two periods of work with lunch between. e.g. B 1 07:00 2 16:30 3 17:00 4 23:00 5 =IF(B2<A1,0,B2-A1) 6 =IF(B3>A1,B4-B3,B4-A1) 7 =B5+B6 I separated the two sessions for clarity. You could add those two IF statements in one cell. In words – person works 7am to 4.30pm then takes 30 minutes for late lunch! Staggers back after lunch to work 5pm to 11pm. B5 says check - is first knock off time before 4pm, if ‘yes’ add 0, if ‘no’, add the difference between 4pm and his knock off time B6 says check - is second shift start after 4pm, if ‘yes’ add knock-off minus second start time, if ‘no’ add second knockoff time minus 4pm B7 sums the two “after 4pm” amounts. I tried it with some sample shifts and it seems to give correct answers, but let me know if you find any cases that don’t work. And I assumed that shifts are all within the same day. It gets a little harder if you work right through the night! Enjoy! |
coldot (6847) | ||
| 1 | |||||