Solved

Determine time elapsed based on working hours and working days only

Posted on 2013-02-06
2
272 Views
Last Modified: 2013-02-06
I am attempting to build a formula that will take Start date and time (represented in the following formula as column E) and the end date and time (represented by column J) and give me the total time worked based on an SLA that only counts Monday through Friday from 08:00 to 17:00, and does not count Saturdays, Sundays, or holidays.
Here is the formula I have cobbled together from other examples found elsewhere:

=MEDIAN(8/24,MOD((J161),1),17/24)-MEDIAN(8/24,MOD((E161),1),17/24)+(NETWORKDAYS((E161),(J161),Holidays)-1)*1/2))

A list of holidays has been created and the range named as 'holidays' for simplicity.  The list of holiday dates the NETWORKDAYS formula uses are:
-Jan 1
-Feb 18
-Mar 29
-May 20
-Jul 1
-Sep 2
-Oct 14
-Nov 11
-Dec 25
-Dec 26

Examples of calculations that should be returned if this formula works correctly:

Start d/t: 2013/01/09 10:05:00
End d/t: 2013/01/09 13:15:00
Total time worked should be 3h 10m 0s, as the start date and end date were on a Wednesday, completed within work hours (08:00-17:00).

Start d/t: 2013/01/05 20:05:00
End d/t: 2013/01/06 09:10:00
Total time worked should be 0h 0m 0s, as the start and end were both on a weekend.

Start d/t: 2013/01/06 20:05:00
End d/t: 2013/01/07 09:10:00
Total time worked should be 1h 10m 0s, as the start date was a Sunday, and it completed after 08:00 on Monday morning.

Start d/t: 2013/01/18 08:55:00
End d/t: 2013/01/22 08:45:00
Total time worked should be 17h 50m 0s, as the start date was a Friday during the workday, and it completed Tuesday almost at the same time, but the after-work hours and weekend hours are not included.

Start d/t: 2013/02/15 12:00:00
End d/t: 2013/02/19 13:30:00
Total time worked should be 10h 30m 0s, as the start date was a Friday at midday, and completed on the following Tuesday afternoon, but Monday not be counted because it was a holiday.

Sometimes my formula works but often it doesn't and it's typically when the calculation spans multiple days that it doesn't.

Any assistance would be appreciated.
0
Comment
Question by:brilliantidea
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
ID: 38861434
Try this formula to get elapsed work hours

=(NETWORKDAYS(E2,J2,Holidays)-1)*("17:00"-"8:00")+IF(NETWORKDAYS(J2,J2,Holidays),MEDIAN(MOD(J2,1),"8:00","17:00"),"17:00")-MEDIAN(NETWORKDAYS(E2,E2,Holidays)*MOD(E2,1),"8:00","17:00")

format result cell as [hh]:mm:ss

see attached showing your examples and one other....

The formula works for any start/end times, even at weekend/holidays etc. as long as J2>=E2

regards, barry
business-hours.xls
0
 

Author Closing Comment

by:brilliantidea
ID: 38861702
Thanks for your prompt reply.  It worked perfectly.  Thanks Barry.
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question