Find number of working days between dates...

[mrexcel27]

Hi all, wondering if you could help me automate this...

We send out dozens of items per day. The shipping company provides tracking information for each package so we can see when the item is delivered. Depending on the service requested by the customer, a varying length of service duration can be expected. A number of days after the expected delivery date, the item is considered to be delayed and the shipping fee can be claimed back from the shipping company. Currently, this is done manually and as you can imagine, it takes some time.

So I had the following idea....

A1 to H1 is the header row. Customer details are in A2 and B2 and are for reference only, taking no part in the formula. The 'Ship Date' is in C2. The 'Service Used' is in D2. A VLOOKUP function is used to return the expected service duration 'Ship Time' in E2.

Cell E2 formula =VLOOKUP(D2,$M$2:$N$4,2,FALSE)

M2 to M4 is Next Day, Express and Standard, and N2 to N4 is 1, 3 and 4

The 'Due Date' is in F2, 'Ship date' plus 'Ship Time', including working days only.

Cell F2 formula currently =C2+E2

In G2 is 'Received Date' which gives the actual date of delivery according to the tracking number. H2 is 'Delayed' an =IF function returning the number of working days delayed after the 'Due Date' has elapsed, or 'ON TIME'.

Cell H2 formula currently =IF(G2<=F2, "ON TIME", G2-F2)

I am having problems with the F2 and H2 formulas as the shipping company is accounting for Monday to Saturday inclusive as working days, so =NETWORKDAYS does not appear to work as it excludes Saturdays as well as Sundays.

What are the formulas I need to use in F2 and H2? Thanks

[etaf]

Networkdays.intl() has options to do a Mon-Sat workday
However, A spreadsheet as mentioned in the yellow banner at top of thread would be useful , with some data and examples of the desired results

[Pete_UK]

Instead of that lengthy description, it would be much easier to follow what is happening (and to try out different approaches) if you attached a sample Excel workbook - the yellow banner at the top of the screen describes how to.

Basically, you want to add on to the normal NETWORKDAYS function the number of Saturdays which occur between the start and end dates, but I think there is another function (NETWORKDAYS.INTL) in later versions of Excel which allows you to specify the working week in your area.

Hope this helps.

Pete

[mrexcel27]

Hi, thanks for the advice guys, unable to post spreadsheet as it is quite large and has a lot of confidential customer data inside and the worksheet metadata identifies the company, sure will work it out eventually.

[etaf]

you could just create a dummy sheet with non-confidential info, we don't need to see the full version
and as mentioned networkdays.intl() may do what you need