Stuck on calculating working hours more complicated than expected

Hallo everybody

Since I added the below problem I have come across the the formula below, however I still cant get it to work if possible is here anybody that can tell me what I am doing wrong.

=(NETWORKDAYS.INTL(D4+E4,11,+$A$501:$A$558)-1)*("17:00"-"7:00")+IF(NETWORKDAYS.INTL(E4,E4,11,$A$501:$A$558),MEDIAN(MOD(E4,1),"7:00","17:00"),"17:00")-MEDIAN(NETWORKDAYS.INTL(D4,D4,11,$A$501:$A$558)*MOD(D4,1),"7:00","17:00")

I dont understand it looks correct.

I would really appreciate any help.



Hi everybody

Saying that I'm a bit stuck would be an understatement, sooooo I would realy apreciate if someone could help me out on this one.

I need to calculate the time it take a staff member to do a task problem is that a task may take several days to complete, soooooo calculating the time should'nt be a problem or so I thought.

Until I noticed that my simple sum formula was working so well that it was even including weekends holidays and non working hours, BRILLIANT!!!

If anyone could assist me with removing the weekends, holidays and non working hours I would realy appreciate it.

Im currently ussing Excel 2010.

D4=Time Started (Format yyyy/mm/dd hh:mm)
E4=Time End (Format yyyy/mm/dd hh:mm)
F4=Answer (Format [h]:mm)

Working hours are from 07:00 to 17:00

If there is a way to avoid VBA it would be great as I have no idea how to use it. If not I will have to learn how to use it very quickly.

Thank you

Book1.xlsx

Thanks boet you bloody legend almost thought I was lost in the forsaken desert of excel.

Your formula is the closest Ive been for 5 days now, however it is still not working I attached a copy of the file you will see as soon as you enter the = into the eqation it still gives an error. Is it possibly the way I entered the Weekends and holidays in cell range A501 to A580?

Thanks for the reply

Hi daddylonglegs

Please help :S

I'm trying to calculate the time difference in [h]:mm:ss between 2 dates and times but with the following conditions:

1. Exclude weekends
2. Within the Business hours of 07:00- 20:00 Monday to Friday

By doing this I will be able to accurately see the lead time for incident resolutions.

I've used the following calculations (which I found online) in the hope of getting close to an accurate formula, BUT the first formula doesn't exclude weekends therefore making the time difference incorrect and not sure how to exclude weekends within the formula...plus there are times where it randomly results in "######" and not sure as it appears to be calculating negative numbers and I'm not sure how to rectify this within the formula e.g. difference between 03/06/2015 00:19:34 and 03/06/2015 00:30:46 = -0.270300926 which shows as "####" and should be 0.007777778 or 0:11:12.

Please see attached for data worked examples.

FORMULA 1... see attached for details, I would have written it here, but I kept getting an error message when trying to submit...

Worked example: Current formula does not exclude weekends

An open time of Saturday 17/05/2014 02:02:00 and a completion time of Monday 19/05/2014 10:00:00 should equal 3:00:00. BUT the above formula counts the weekend giving an incorrect duration of 23:00:00.

FORMULA 2...see attached for details, I would have written it here, but I kept getting an error message when trying to submit...

This formula uses the Median function but I'm worried this is impacting how it calculates the lead time e.g. I've noticed the formula results in mins and seconds being cut off e.g. it will calculate the difference between 22/10/2015 03:44:23 and 22/10/2015 03:49:55 should come back with 0:05:32 but comes back with 00:00:00. (See attached for examples)

Formula 3: The closest thanks to your post above!! (see attached for details, I would have written it here, but I kept getting an error message when trying to submit...)
This one results in less "########'s" but there are still some.

E.g. Difference between 25/10/2015 04:22 and 25/10/2015 04:27 shows as ###### or in numbers -0.996712963 when it should be 0:04:44, but I can only get this by removing the -1 but then when I apply this to the other entries, by doing that it adds 13hrs (i.e. 1 working day) to the originally correct answers.

Please help!

Many thanks in advance

Dear Excel Gurus,


I need your help and expertise for some formulas to calculate SLA time for our Helpdesk ticket.
I've some issue calculating between 2 dates and times with the following condition:

Working hours are from 08:00AM to 17:00PM
Lunch time: 12.00PM-13.00PM

1. Exclude Weekends
2. Within the Business hours of 08:00- 17:00 Monday to Friday
3. Exclude Public holiday(Holiday list)

The Formula i used

=(NETWORKDAYS(G2,H2,HolidayList)-1)*("17:00"-"08:00")+ IF(NETWORKDAYS(H2,H2),MEDIAN(MOD(H2,1),"17:00","08:00"),"17:00")- MEDIAN(NETWORKDAYS(G2,G2)*MOD(G2,1),"17:00","08:00")

But above formula failed to calculate If the ticket created after Working hour, closed ticket before Working hour started, calculate including Lunch time and other specific condition the result become 0:00 (hh:mm).

Sample 1:
G2: 5/12/20 17:05 PM
H2: 5/12/20 17:09 PM

Sample 2:
G2: 5/1/20 7:40 AM
H2: 5/4/20 17:16 PM

Sample 3:
G2: 5/20/20 17:39 PM
H2: 5/20/20 7:51 AM

For Sample 1- the formula still calculating the SLA by time consume even created and closed between non-working fours.
Sample 2, SLA will calculating by Working hours.
Sample 3, the SLA will set default total hour = 3hour.

Thank you very much...Please help.