Calculating Hours Between Two Date - Including Time

[azieli02]

I have a spreadsheet where I am trying to calculate the time between when a report is submitted to when the final is sent to the customer excluding weekends. I am currently using a NETWORKDAYS.INTL formula, but this is only calculating between the dates. For example:

Cell A2 = 2/28/16 8:25 PM
Cell B2 = 3/3/16 8:13 AM

I should be getting that there is a difference of 80 Hours and 12 Minutes when excluding weekends. What I am getting instead is 59 hours and 48 minutes. The formula I am using is subtracting a full 24 hours instead of the 3 hours and 35 minutes it should be. This is the formula I am using:

=NETWORKDAYS.INTL(A2,B2)-1-MOD(A2,1)+MOD(B2,1)

Can someone help me figure out how to get this to work with the time so it excludes Saturday at 12:00 AM until Sunday at 11:59 PM?

I appreciate the help!

[mehmetcik]

Please Login or Register  to view this content.

I just spotted the bit about ignoring Saturday-Sunday specified hours.

That gets complicated. I would have to use a userdefined function for that.

[CAntosh]

I think this works? I've been toying around with it and it appears to hold up. Give it a try...

=IF(WEEKDAY($B4,2)>5,IF(WEEKDAY($A4,2)>5,NETWORKDAYS($A4,$B4),NETWORKDAYS($A4,$B4)-(MOD($A4,1))),IF(WEEKDAY($A4,2)>5,NETWORKDAYS($A4,$B4)-(1-MOD($B4,1)),NETWORKDAYS($A4,$B4)-(1-MOD($B4,1))-MOD($A4,1)))

[Phuocam]

Try =NETWORKDAYS.INTL(A2,B2)-1-MOD(A2,1)*(WEEKDAY(A2,2)<6)+MOD(B2,1)*(WEEKDAY(B2,2)<6)

[azieli02]

Thank you this worked beautifully. I went with Phuocam's suggestion though as it was very similar. I was so close on my own, and appreciate the help!

[azieli02]

Try =NETWORKDAYS.INTL(A2,B2)-1-MOD(A2,1)*(WEEKDAY(A2,2)<6)+MOD(B2,1)*(WEEKDAY(B2,2)<6)

Thank you Phuocam! Cantosh had another beautiful solution, but yours was a little simpler. I just needed to add the weekday portion it appears!

[Phuocam]

You're welcome, good luck!