Stumped with Trying to come up with Excel Formula re: Dates

[quakeworld]

hi, i'm stumped trying to come up with an Excel formula to keep track of an employee's time spent on a task. we work graveyard shift from 8pm to 4:30 am, M-F. Normally just subtracting two dates/time would work (start time: 5/10/12 10 pm end time: 5/11/12 1 am would yield 3 hours) but if an employee starts a task at 8pm on a Friday (5/11/12 8pm) and finishes the task on Monday (5/14/12 9pm) that would mean it took 9 (working) hours to finish the task. The thing that messes me up is when Friday crosses over into Saturday (still on Friday shift since it's 3rd shift). i'm stuck trying to come up with something that would exclude Sat (and Sun) hours and to just keep track of working hours hours. Any help is appreciated!

 

[wayliff]

have you tried looking at the "NETWORKDAYS" function?

http://office.microsoft.com/en-us/excel-help/networkdays-HP005209190.aspx

Not sure if it will work but may be a start.

 

[theknight571]

How is the information entered into the spreadsheet?

Can you give an example?

 

[PowerEngineer]

So your first Excel question was just the tip of the iceberg!

A better explanation of your shift work schedule might be helpful. I'm guessing you have three ~8 hour shifts to form a 24/7 operation. I'm also guessing that the person on shift doesn't necessarily turn over all the unfinished work of the next person on shift, which means that several shift might go by before that person is back on shift to finish what he/she started days earlier. Close?

I'm afraid you'll find that Excel is not a very good tool upon which to build a time tracking/scheduling application. I know this because I did this very same thing for a 24/7 shift operation several years ago. Just wait until you run into daylight savings time...

 

[wayliff]

sounds like this might help...
http://www.ehow.com/how_12116309_calculate-hours-excel-excluding-weekends.html

 

[quakeworld]

thanks for the links wayliff but they won't work in my situation. the information is entered manually into an excel spreadsheet. the spreadsheet is a tracker that keeps track of the time an employee spends on a task, in this case, a test case (the employees are testers). we use the time it takes (turn around time or TAT as we call it) as part of their metrics. so for example the tester starts the test case on 5/11/12 at 8 pm (Friday) and finishes on 5/14/12 9 pm (Monday). Counting the time manually, it took the tester all of friday shift (8pm to 4:30 am - 8 hours (.5 goes to break)) and an hour on monday (8pm to 9pm) for a total of 9 hours to execute the test case.

the part that messes me up is when friday crosses over into saturday because it inflates the tester's TAT.

the way they're doing it right now is kinda cumbersome. the current way of doing it in excel is:

cell a1 start time
cell b1 end time

so for ex. start time is 5/11/12 8pm. end time is 5/14/12 9 pm.

they enter it in excel (horizontally) as

start 5/11/12 8pm end 5/11/12 4:30 am ----- formula to get the difference

next row

start time 5/14/12 8pm end 5/14/12 9 pm ----- formula to get the difference


what i wanna do is just to have two dates. the start time and end time without the need for multiple entries.

@Power Engineer - if this is too messy to do in excel what do you recommend i use?

 

[NeoV]

=(NETWORKDAYS(A2,B2)-1)*("20:00"-"4:30")+MOD(B2,1)-MOD(A2,1)

This works when the shift hours don't cross days - like an 8am to 5pm shift, but I can't get it to work with this crossing-days work shift - close but no cigar!