time study

[vthokie2010]

I am trying to put my data from clock in and clock out punches into some kind of table or something similar. Basically I have 10 people working at 10 different stores. i'm trying to compile the data so someone clocks in at 10am and out at 6pm(18:00) it would put this in the table for the specific store showing 1 person worked the hours of 10 11 12 13 14 15 16 17 18. Then when it looks at the next person at the same store and they worked 9:24 am to 2pm(14:00) it would add that person so now I have .6 for the 9, 2@ 10, 2@11, 2@12, 2@13, 2 @ 14, 1 @ 15, 1@16. And continually build on this table from the data I have. I hope that was clear but here is an example of the data I have that I want to get into this format.

[shg]

See attached. There's a single array formula in each row that calls a user-defined function.

you can use the AutoFilter dropdown to see individual stores.

[vthokie2010]

Well that's just sick. Thank you so much and for posting so quickly. I imagine I can just do a pivot table and get it so it will sum up each hour by day and store number. If i wanted to break this down and look at it on a 30 minute incriment instead of hourly would i just divide by 0:30 instead of 1:00 or do I need to go into the code and change Const t1hr? I'm looking over the code to get the logic because this macro stuff is really interesting to me.

[shg]

You'd need to change the constant and change the size of the returned array to 48.

[vthokie2010]

Ok so this works great but I can't figure out how it is working...The Do statement is the part I think i'm getting lost at. I don't see how it is assigning the 1 or <1 value in the array for each specific hour. Maybe I don't understand arrays well enough. If you can explain that would be awesome. Thanks

[vthokie2010]

Nevermind I figured it out. Had a brain lapse on how Dim atOut(0 To 23) As Date worked.

Thanks again!

[shg]

Good job!

Glad it worked for you.