Subtract two times, exclude weekends

[blens1]

I am looking for a formula that subtracts two times from each other. I do not want Saturday and Sunday to be counted in the calculation.

I was trying to use networkdays.intl but when the item arrived on saturday at a specific date and time and was done processing on Monday at a specific time and date the calculation showed over 24 hours where it should be under 24 hours. I would love for the formula to be in hours. Good Luck to whoever figures this out as it is not my first post on this.

[AL1976]

can this help ( http://www.mrexcel.com/forum/excel-q...-weekends.html ) ?

[Kaper]

My proposition:
=NETWORKDAYS.INTL(MAX(A6,WORKDAY.INTL(A6-1,1,1)),B6,1)-MOD(MAX(A6,WORKDAY.INTL(A6-1,1,1)),1)+MOD(B6,1)

And format for instance as [hh]:mm
Please note that in last row you will get negative number, because as opposite to " I do not want Saturday and Sunday ..." the arrival is on Sat and processed on Sunday :-(

Using NETWORKDAYS.INTL is a good idea because it allows to easily take into account holidays.

if you want to have it as a number of hours take
=(the formula_above)*24
and use general format

[blens1]

AL1976 I think that worked out. Give me the week to play around with it and I will get back to you. All I had to do was add =networkdays.INTL

[blens1]

Kaper, you are actually the winner and spot on

[blens1]

Kaper, you are actually the winner and spot on. Do you know how to do incorporate holidays into that?

[Kaper]

both NETWORKDAYS.INTL and WORKDAY.INTL have the optional 4th argument - you can refer to list of holidays there. For instance if the list is in Z1:Z14 then

=NETWORKDAYS.INTL(MAX(A6,WORKDAY.INTL(A6-1,1,1,$Z$1:$Z$14)),B6,1,$Z$1:$Z$14)-MOD(MAX(A6,WORKDAY.INTL(A6-1,1,1,$Z$1:$Z$14)),1)+MOD(B6,1)

would do the job notice that WORKDAY is used twice so $Z$1:$Z$14 appeared 3 times above

[blens1]

Last but not least the formula is not working when the activity happens within the same day. So if you put in the appropriate cells that something arrived 2/3/14 11:16 and then unloaded 2/3/14 23:16 then it shows 1.50 but should be 0.50. This is the same for any activity within the same day. I definitely appreciate your help Kaper!!!!

[Kaper]

May be you shall subtract 1 at the and. Check this out.
Today start and today end means 1 day activity for NETWORKDAYS . Today start (evening) and tomorrow end (morning) means 2 day activity. The NETWORKDAYS cares for days only

[blens1]

I understand. I can't subtract 1 though because then it would subtract 1 for every other instance where the process involves more then 1 day.

[Kaper]

Have you tried:
=NETWORKDAYS.INTL(MAX(A6,WORKDAY.INTL(A6-1,1,1,$Z$1:$Z$14)),B6,1,$Z$1:$Z$14)-MOD(MAX(A6,WORKDAY.INTL(A6-1,1,1,$Z$1:$Z$14)),1)+MOD(B6,1)-1
Try !

[Kaper]

I say: Have you really tried?
I did.
See attachment.

[daddylonglegs]

That works for me, Kaper, but you can simplify a little with this version

=NETWORKDAYS(A6,B6,Z$1:Z$14)-1+MOD(B6,1)-NETWORKDAYS(A6,A6,Z$1:Z$14)*MOD(A6,1)

Assumes that A6 is any time but B6 will be within work hours - multiply by 24 if you want decimal hours otherwise format as [h]:mm