Calculating Elapsed Time within Variable Working Hours

**TIAL** · 05-08-2012, 09:47 AM

Hi there,

I am trying to work out the elapsed time stock stays in my workplace between working hours - this would be simple enough if the working hours were standard every day, but the hours change on weekends.
The hours are Mon - Friday 08:00 - 17:30, Saturday 09:00 - 15:00, Sunday 09:00 - 12:00

Calculating the time if it's on the same day is simple. I'd like excel to be able to recognise the time span and subtract the correct ammount of 'closed' time from the total elapsed.

I have 4 columns - Date in, time in, date out and time out - I would like a fifth column with 'total working hours'.

Any help would be greatly appreciated!

**benishiryo** · 05-09-2012, 10:45 AM

hi TIAL, it would be great if u can upload an eg. basically, your Date In should be combined with Time In & Date Out should be combined with Time Out. u can do that by summing them up. It makes the calculations easier cos Excel stores Dates as numbers & Time as decimals.

**daddylonglegs** · 05-09-2012, 04:45 PM

If start and end times will always be within the working hours then you can set up a table showing the hours for each day and then use this formula for elapsed work hours

=SUMPRODUCT(INT((WEEKDAY(A2-I$3:I$9)+C2-A2)/7),L$3:L$9)+D2-B2+LOOKUP(WEEKDAY(A2),I$3:J$9)-LOOKUP(WEEKDAY(C2),I$3:K$9)

See attached example

If your start and end times might be outside work hours that can be accommodated too but it requires a slightly more complex version of that formula

**nimamoradi** · 11-10-2013, 06:02 AM

your calculation is wrong because it doesnt take into account the half hours from the trading day

**daddylonglegs** · 11-10-2013, 07:24 AM

Originally Posted by nimamoradi

your calculation is wrong because it doesnt take into account the half hours from the trading day

example, please

**nimamoradi** · 11-10-2013, 07:35 AM

sorry my brain is fried because im working with a similar problem, my manual calculation was wrong. i retract my statement

**nimamoradi** · 11-10-2013, 07:44 AM

sla ap.xlsx

hi DLL
is it possible to adjust your formula to work with different open / close times based on the store id / store type?

see attachment as modified from your original
perhaps by using an if statement or something?
i have over 5000 lines for stores with different open /close times and this is so close to solving my problem

thanks in advance
nima

**daddylonglegs** · 11-12-2013, 09:17 PM

Sorry, I haven't had much time to look at this....

I used named ranges to name the 3 tables Type1, Type2 and Type3 and then I used this formula for the hours

=SUMPRODUCT(INT((WEEKDAY(C2-INDEX(INDIRECT("Type"&B2),0,2))+E2-C2)/7),INDEX(INDIRECT("Type"&B2),0,5))+F2-D2+LOOKUP(WEEKDAY(C2),INDEX(INDIRECT("Type"&B2),0,2),INDEX(INDIRECT("Type"&B2),0,3))-LOOKUP(WEEKDAY(E2),INDEX(INDIRECT("Type"&B2),0,2),INDEX(INDIRECT("Type"&B2),0,4))

see attached

Note that the formula only works when all start/end times/dates are within working hours, so row 14 is incorrect, for example, because the finish time is 09:31 on a Wednesday and the store hours for type3 don't begin until 11:00 on a Wednesday.

If you want I can adjust the formula so that it works whether the times are inside or outside working hours

Calculating Elapsed Time within Variable Working Hours

LinkBack

Thread Tools

Rate This Thread

Display

Calculating Elapsed Time within Variable Working Hours

Re: Calculating Elapsed Time within Variable Working Hours

Re: Calculating Elapsed Time within Variable Working Hours

Re: Calculating Elapsed Time within Variable Working Hours

Re: Calculating Elapsed Time within Variable Working Hours

Re: Calculating Elapsed Time within Variable Working Hours

Re: Calculating Elapsed Time within Variable Working Hours

Re: Calculating Elapsed Time within Variable Working Hours

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions