Elapsed work hours in spreadsheet

NancyP

New Member
Joined
Jan 18, 2005
Messages
2
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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

martinee

Well-known Member
Joined
Nov 4, 2003
Messages
960
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

Board Regular
Joined
Oct 21, 2002
Messages
184
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

New Member
Joined
Jan 18, 2005
Messages
2
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





martinee said:
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.
 

Forum statistics

Threads
1,148,224
Messages
5,745,472
Members
423,953
Latest member
MrC54

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top