Difference between two dates

**ckk403** · 01-24-2013, 10:44 AM

Help! I have two different sources of people trying to do the same as me, but neither appear to be working and I am at a lost for where I am going wrong.

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

http://www.excelforum.com/excel-gene...-holidays.html

I am trying to calculate the hours and minutes between two dates and times but only within business hours. The business hours are 8:00AM - 4:00PM. Using the first link there is an example workbook that I tried using. I put in 1/10/2013 11:03:43:PM as the received date, and 1/11/2013 8:26:14 AM as the completed date. When I plug these numbers into the sample workbook on the first link it spits out 6.12 hours. If we are only calculating business hours the start date would be 1/11/2013 8:00:00AM and it would be completed at the same time for a completion time or duration of 26 minutes and 14 seconds.

In the second link and the download "TicketOpenAndClosedHours2.xlsx‎" I plug in the same date and time and it kicks out 6.02 for the duration.

What am I missing? What am I doing wrong? Shouldn't these calculations result in a 26 minutes due to business hours?

**shg** · 01-24-2013, 11:30 AM

Please Login or Register  to view this content.

The formula in B8 is

=(INT(B7) - INT(B6)) * ($B$3 - $B$2)
+ MEDIAN(MOD(B7, 1), $B$2, $B$3)
- MEDIAN(MOD(B6, 1), $B$2, $B$3)

The cell is formatted as [h]:mm:ss

**ckk403** · 01-24-2013, 01:24 PM

This works perfectly. I am very pleased with the formula. Unfortunately I am not pleased with the results. It turns out every weekend there are 16 hours of randomly worked time. Example is we received a date of 1/11/2013 3:41:30 PM, 19 minutes before close. It shouldn't be worked until 1/14/13 8:00:00 AM, instead it was worked on at 1/12/2013 9:44:57 PM. So can you think of a way to add in a clause that if completed date is a weekend and completed before Monday it isn't late or even results in a 2. Part one is finding the duration, part two is analyzing how many were completed in bucket intervals. I can take care of the bucket intervals if I didn't have this 16 hours of worked hours completing things outside of business hours.

**ckk403** · 01-24-2013, 03:29 PM

Never mind I was able us IF(OR(Text(cell,"ddd"="Sat",Text(Cell,"ddd"="Sun),"02:00",your formula. Thank you for all of the help.

**ckk403** · 01-24-2013, 05:33 PM

I did run into another problem I didn't realize this didn't work on the weekends (or I did something wrong).

Please Login or Register  to view this content.

Since it was received on Friday after 4:00PM, it shouldn't be in until 8:00AM on Monday (the 14th) so the time elapsed should be 4:26.

**shg** · 01-24-2013, 05:59 PM

Please Login or Register  to view this content.

From Daddylonglegs:

= (NETWORKDAYS(B4, B5)-1) * ($B$2 - $B$1)
+ IF(NETWORKDAYS(B5, B5), MEDIAN(MOD(B5, 1), $B$2, $B$1), $B$2)
- MEDIAN(NETWORKDAYS(B4, B4) * MOD(B4,1), $B$2, $B$1)