Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculate time worked based on start / end times & working hours
Ok, this one is a biggie... if you call pull this one off, I'll hail you as
the king of excel and sing your praises far and wide... no sweat if there are no takers... just figured I'd ask! I have data that looks like this: ID | Task | Start | End | Duration ------------------------------------------------------------- 01 | sift | 6/24/09 10:30 PM | 6/25/09 2:30 AM | 0.166666667 01 | stack | 6/25/09 2:31 AM | 6/25/09 8:00 AM | 0.228472222 01 | load | 6/25/09 8:01 AM | 6/25/09 3:00 PM | 0.290972222 02 | sift | 6/25/09 3:01 PM | 6/25/09 9:00 PM | 0.249305556 02 | stack | 6/25/09 9:01 PM | 6/26/09 1:00 AM | 0.165972222 02 | load | 6/26/09 1:01 AM | 6/26/09 8:00 AM | 0.290972222 I need to figure out how much total time was spent on a given ID (or job) considering the actual working hours. Of course, working hours are a bit weird in that they cross days - specifically: Mon - 6AM to 11:59PM [19hrs] Tue - 12AM to 4AM, 6AM to 11:59PM [22hrs] Wed - 12AM to 4AM, 6AM to 11:59PM [22hrs] Thu - 12AM to 4AM, 6AM to 11:59PM [22hrs] Fri - 12AM to 4AM, 6AM to 6PM [15hrs] Sat - 6AM to 6PM [12hrs] Sun - [0hrs] Crazy right? Anyway, using the info above, I'd expect the calculation to show that ID "01" took a total of 14hrs, 28mins. This is because the raw sum of the durations comes to 16hrs, 28mins but includes 2 hours of non-working time. Make sense? Thanks in advance! -meat p.s. love this forum! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculate time worked based on start / end times & working hours
Meatlightning,
This is what I came up with: ID Task StartDate StartTime EndDate EndTime Duration Totals 01 sift 6/24/09 22:30 6/25/09 2:30 0.1667 01 stack 6/25/09 2:31 6/25/09 8:00 0.2285 01 load 6/25/09 8:01 6/25/09 15:00 0.2910 16.47 02 sift 6/25/09 15:01 6/26/09 1:00 0.4160 02 stack 6/25/09 21:01 6/26/09 1:00 0.1660 02 load 6/26/09 1:01 6/26/09 8:00 0.2910 20.95 Starting in cell A1 with "ID",cell B1-"Task" and so on. Column "C2 thru C7" format as Date, same "E2 thru E7". Column "D2 thru D7" format as time (13:30), same with "F2 thru F7". Column "G2 thru G7" format as number, I used 4 decimal places. Add column heading "Totals" In Cell H4 put formula "=(SUMIF(A2:A4,"+01",G2:G4)*24)" without "". In Cell H7 put formula "=(SUMIF(A5:A7,"=02",G5:G7)*24)" without "". Hope this helps, or gets you headed in the right direction. "MeatLightning" wrote: Ok, this one is a biggie... if you call pull this one off, I'll hail you as the king of excel and sing your praises far and wide... no sweat if there are no takers... just figured I'd ask! I have data that looks like this: ID | Task | Start | End | Duration ------------------------------------------------------------- 01 | sift | 6/24/09 10:30 PM | 6/25/09 2:30 AM | 0.166666667 01 | stack | 6/25/09 2:31 AM | 6/25/09 8:00 AM | 0.228472222 01 | load | 6/25/09 8:01 AM | 6/25/09 3:00 PM | 0.290972222 02 | sift | 6/25/09 3:01 PM | 6/25/09 9:00 PM | 0.249305556 02 | stack | 6/25/09 9:01 PM | 6/26/09 1:00 AM | 0.165972222 02 | load | 6/26/09 1:01 AM | 6/26/09 8:00 AM | 0.290972222 I need to figure out how much total time was spent on a given ID (or job) considering the actual working hours. Of course, working hours are a bit weird in that they cross days - specifically: Mon - 6AM to 11:59PM [19hrs] Tue - 12AM to 4AM, 6AM to 11:59PM [22hrs] Wed - 12AM to 4AM, 6AM to 11:59PM [22hrs] Thu - 12AM to 4AM, 6AM to 11:59PM [22hrs] Fri - 12AM to 4AM, 6AM to 6PM [15hrs] Sat - 6AM to 6PM [12hrs] Sun - [0hrs] Crazy right? Anyway, using the info above, I'd expect the calculation to show that ID "01" took a total of 14hrs, 28mins. This is because the raw sum of the durations comes to 16hrs, 28mins but includes 2 hours of non-working time. Make sense? Thanks in advance! -meat p.s. love this forum! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculate time worked based on start / end times & working hours
Meat,
This isnt so tough, just tedious. Your opening calculation, which yields the actual number that Excel uses is a good start. Convert that to minutes with a Lookup table. One minute equals 0.000694386573741212. Add it to your previous total to make as long a list of minutes as you need for the table. The lookup keyed from the duration will return the number of minutes for each record. Total the minutes for each ID, task or whatever category you want. You can then convert the minutes to decimal hours with arithmetic division. The decimal remainder can then be converted to minutes is you like. KIOhio "MeatLightning" wrote: Ok, this one is a biggie... if you call pull this one off, I'll hail you as the king of excel and sing your praises far and wide... no sweat if there are no takers... just figured I'd ask! I have data that looks like this: ID | Task | Start | End | Duration ------------------------------------------------------------- 01 | sift | 6/24/09 10:30 PM | 6/25/09 2:30 AM | 0.166666667 01 | stack | 6/25/09 2:31 AM | 6/25/09 8:00 AM | 0.228472222 01 | load | 6/25/09 8:01 AM | 6/25/09 3:00 PM | 0.290972222 02 | sift | 6/25/09 3:01 PM | 6/25/09 9:00 PM | 0.249305556 02 | stack | 6/25/09 9:01 PM | 6/26/09 1:00 AM | 0.165972222 02 | load | 6/26/09 1:01 AM | 6/26/09 8:00 AM | 0.290972222 I need to figure out how much total time was spent on a given ID (or job) considering the actual working hours. Of course, working hours are a bit weird in that they cross days - specifically: Mon - 6AM to 11:59PM [19hrs] Tue - 12AM to 4AM, 6AM to 11:59PM [22hrs] Wed - 12AM to 4AM, 6AM to 11:59PM [22hrs] Thu - 12AM to 4AM, 6AM to 11:59PM [22hrs] Fri - 12AM to 4AM, 6AM to 6PM [15hrs] Sat - 6AM to 6PM [12hrs] Sun - [0hrs] Crazy right? Anyway, using the info above, I'd expect the calculation to show that ID "01" took a total of 14hrs, 28mins. This is because the raw sum of the durations comes to 16hrs, 28mins but includes 2 hours of non-working time. Make sense? Thanks in advance! -meat p.s. love this forum! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculate time worked based on start / end times & working hours
Thanks FloMM2 & KIOhio! Really appreciate it.
I think you are missing what I need? Or maybe I'm just not seeing it in your solutions? I need to subtract any non-working time from the durations of my various tasks, then add them all up based on their ID. Basically, the timekeeper is dumb and doesn't account for when people are actually working on tasks. So, if a task starts on Saturday and completes on Monday, the timekeeper doesn't account for the fact that no work was done on Sunday (day off!) and says the task took 3 days (when it really took 2). What I can't wrap my head around is - how do you take a start and stop timestamp and find the duration, then determine what portion of that duration was not applicable and subtract that out. Further complicating things (for me at least) is that the working hours are not uniform. I basically need to look at each record's start & end times, figure out the day of the week, then based on that determine the non-working hours, then figure out what, if any, portion of that time was recorded by the timekeeper and subtract it out. Clear as mud no? Anyway, thanks again! -meat |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate how much time falls between set start and stop times | Excel Worksheet Functions | |||
Creating Bi-Weekly Time Sheet to Calculate Hours Worked | Excel Discussion (Misc queries) | |||
Calculate Total hours worked during workdays within given shift time. | Excel Discussion (Misc queries) | |||
Drivers Hours Timesheet - Calculate Hours Worked on Weekly Basis | Excel Discussion (Misc queries) | |||
Calculate working hours from a start and finish time over several | Excel Worksheet Functions |