Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 27
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 207
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 7
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 27
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculate how much time falls between set start and stop times Polly Excel Worksheet Functions 17 September 3rd 08 12:12 PM
Creating Bi-Weekly Time Sheet to Calculate Hours Worked nbslarson Excel Discussion (Misc queries) 2 August 30th 07 02:10 PM
Calculate Total hours worked during workdays within given shift time. noname Excel Discussion (Misc queries) 2 April 8th 07 06:28 PM
Drivers Hours Timesheet - Calculate Hours Worked on Weekly Basis Graham Excel Discussion (Misc queries) 2 January 28th 07 09:40 PM
Calculate working hours from a start and finish time over several Jive Excel Worksheet Functions 1 September 25th 06 12:46 PM


All times are GMT +1. The time now is 09:56 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"