Elapsed work hours in spreadsheet

[NancyP]

Somebody help me PUH-LEESE!!! I've been searching through old posts and can't find what I need or simply don't understand the resolutions offered. (Yes, I'm a novice here!) So, here's what I need to do:

I need to calculate the time difference between two timestamps that were captured from a database. The results should be displayed in dd:hh:mm format, be based on an 8 hour workday, and exclude weekends and holidays. Here is an example of the columns being used:

Group Assign Time Exempt Date
12/1/2004 9:39 12/9/2004 12:13

Thanks in advance for your help!
-Nancy

 

[martinee]

How are we to know how what time is "Quitting time/starting time" for the first and last days. for example:

We can assume that for 12/2-12/8 there are 8 hours a day (less 2 days for a weekend). = 5 days * 8 hrs = 40 hours.

But for the first day, they start at 9:39 (am?) and when do they quit? Do we count a full 8 hours on this day. Or should we assume the workday is 9 - 5?

Some clarification please.

 

[tbird]

You can use the function NETWORKDAYS to calculate the number of workdays between two dates. It will also automatically subtract any holidays if you add that argument to the function and have a list of those dates in a range in the workbook somewhere. You must activate the Analysis Toolpack AddIn under the Tools Menu item first to be able to find this function. If you need more help, post back here.

 

[NancyP]

Thanks for the quick reply. To clarify:

The time stamps being used are from a service ticket tracking system. The "group assign" time is when a service ticket was assigned to a group. The "exempt date" is when the clock was stopped on that ticket for a particular reason. So in essence, I'm trying to determine how many business hours elapsed from when a service ticket was assigned to when it was marked for exemption, excluding weekends and holidays.

Work day hours are a normal 8 hour day commencing at 8 a.m. GMT and ending at 4 p.m. GMT.

Thanks for your help,
-Nancy

How are we to know how what time is "Quitting time/starting time" for the first and last days. for example:

We can assume that for 12/2-12/8 there are 8 hours a day (less 2 days for a weekend). = 5 days * 8 hrs = 40 hours.

But for the first day, they start at 9:39 (am?) and when do they quit? Do we count a full 8 hours on this day. Or should we assume the workday is 9 - 5?

Some clarification please.

 

[tbird]

Nancy-
Check out this link to Chip Pearson's site for formulas to calculate the net workdays and hours between two dates. I tried one of his formulas and it worked well.

http://www.cpearson.com/excel/DateTimeWS.htm