MrExcel.com - Your One Stop for Excel Tips & Solutions

Removing weekend dates for a formula that calculates the number of days between 2 dates

Posted by gavin on May 08, 2001 3:03 AM

Hi,

Can anyone please help me.

I have a formula which calculates if an order was received b4 15.00hrs whether it was shipped the next day and also if not whether it was shipped the following day from that!! ( Its used for SLA calculations)

Formulas as follows:

(Shipped next day for orders received b4 15.00hrs, formula gives me a 1 if ok.)

=IF(ISBLANK(A2),"",IF(AND(C2<1500,(A2-B2)<2),1,IF(AND(C2<1500,(A2-B2)>1),0,"")))

(shipped within 2 days of receipt, again gives a 1 if ok)

=IF(ISBLANK(A5),"",IF((A5-B5)<3,1,0))

Currently it includes weekends, so does anyone know how to exclude them!

Many Thanks for all that have read this message this far!

Gavin

Posted by JAF on May 08, 2001 3:28 AM

Gavin

I'm not clear from your formulas which cells contain the dates, but the function you need is NETWORKDAYS.

Rather than simply specifying EndDate - StartDate, if you use =NETWORKDAYS(StartDate,EndDate), this will exclude weekends.

You can also specify public holiday dates to be excluded as well.

The online help for NETWORKDAYS should give you any pointers if the above is not clear.


JAF

Posted by Gavin on May 08, 2001 4:33 AM

JAF,

Thank you very much for the advice, much appreciated

Regards

Gavin

Posted by Aladin Akyurek on May 08, 2001 5:57 AM

Gavin,

It seems to me that you could use NEETWORKDAYS (activate Analysis Toolpak via Tools|Add ins, if you don't have it).

Assuming that

A2 contains date order received
C2 contains hour order received
B2 contains date of shipment

=IF(ISNUMBER(A2),IF(C2<1500),NETWORKDAYS(A2,B2),"")

This gives you number of days between receipt and shipment involving orders received before 1500.

You can then categorize the result of this formula any way you wish.

Note. NETWORKDAYS enables you to exclude holidays too.

Hope this helps.


Aladin

Posted by Aladin Akyurek on May 08, 2001 6:03 AM

Hmm..

Jaf,

I see you already said what is needed. I had the response ready but had no time to submit -- just run to give a 2 hours class. Back from the class, I simply hit submit, hence the duplication.

Cheers.

Aladin