Need help with modifying Excel WORKDAY function

Topic

New Reply

I need some help please with modifying a WORKDAY function in Excel. I am using Excel 2007 (UK version). My dates are in the UK format dd/mm/yyyy. Please see attached Excel spreadsheet. The aim of the spreadsheet is to calculate a delivery date for a user based on the Order Date and Working Day Lead Time (cell B4) or Calendar Day Lead Time (cell B18); plus calculate a working day lead time into calendar days (row 9), which I have already done. There are only two fields the user needs to enter. Either cells B3 and B4, or B17 and B18 depending on whether their supplier quoted them in working days or calendar days.

There are 5 shippings services and each has its own transit time to move the goods from the USA to the UK. The formulae for the AOG shipping service delivery date will need to exclude just Bank Holidays, whereas the other four shipping services will all need to exclude weekends AND Bank Holidays. For clarity,the cells highlighted in yellow are the ones I need help with please.

For an AOG shipping service, what formula do I need in cell B8 (Delivery Date) to be able to do B3+B9+B7 but if this date falls on a Bank Holiday (in the range Z22:Z40), it should move to the next day (which CAN be a Saturday or Sunday) but can’t be a Bank Holiday.

Here is an example to demonstrate what needs to happen:

Order Date = 18 Dec 2015
Working Day Lead Time = 3 (which takes us to 23 Dec)
Calendar Day Lead Time = 5 (as the 19th and 20th is a weekend)
Delivery Date = Saturday 26 Dec 2015 (23 Dec + 2 days transit for AOG takes us to 25 Dec but deliveries cannot take place then as its a Bank Holiday)

For the IOR, Critical, Routine and Sea Freight Routine shipping services, the Delivery Date (in cells C8, D8, E8, F8) needs be B3(Order Date) +C9 (Calendar Days Lead Time) +C7 (transit Time). If the resulting date falls on a weekend or a day that is a Bank Holiday (in range Z22:Z40), move the delivery date to the next day.

Here is an example to demonstrate what needs to happen for an IOR shipping service:

Order Date = 18 Dec 2015
Working Day Lead Time = 3 (which takes us to 23 Dec)
Calendar Day Lead Time = 5 (as the 19th and 20th is a weekend)
Delivery Date = Tuesday 29 Dec 2015 (23 Dec + 2 days transit for IOR takes us to 25 Dec but deliveries cannot take place then as its a Bank Holiday, nor can they take place on weekend of 26/27 Dec, nor can they take place on Monday 28 Dec as this too is a Bank Holiday, so the next date is 29 Dec)

Any help you can give me with the formulas would be much appreciated.

Thanks,
Mark

Reply | Quote

Replies

Mark,

One solution is as follows:

1. Create a list of holidays that must be excluded, In this example, I created 5 holiday dates in cells K3:K7. They can be hidden or placed on another sheet
2. IOR, Critical, Routine and Sea Freight Routine shipping services: In cell C8, enter the formula =WORKDAY($B$3,$B$4+C$7,$K$3:$K$7) then copy across to F8
2. IOR, Critical, Routine and Sea Freight Routine shipping services: In cell C22, enter the formula =WORKDAY($B$17,$B$18+C$21,$K$3:$K$7) then copy across to F22
3. AOG shipping service: A User Defined Function (UDF). Place the following code in a standard module:

Code:

Public Function WorkdayX(start_date As Date, days As Integer, Optional holidays As Range) As Date
For I = 1 To days
    If WorksheetFunction.CountIf(holidays, start_date + 1) > 0 Then
            start_date = start_date + 1
            I = I – 1
    Else: start_date = start_date + 1
    End If
Next I
WorkdayX = start_date
End Function

4. In cell B8, enter the formula =WorkdayX(B3,B4+B7,K3:K7)
5. In cell B22, enter the formula =WorkdayX(B17,B18+B21,K3:K7)

If moving the holiday list to another range or sheet, adjust the range in the formulas

42878-Miller1

HTH,
Maud

Reply | Quote

When creating this, I never saw the holiday list you already assembled in column Z nor the formulas present in rows 9 and 23!

Reply | Quote

Hi Maud,

Thanks for taking the time to reply. Unfortunately neither the UDF you created for the AOG service nor the formulas you entered in C8 across to F8 are giving the correct result. The formula in B8 seems to be ignoring the fact it needs to add the transit time even though you did specify ‘B7’.

I’ve modified the spreadsheet slightly (see attached) from what I posted yesterday by adding a holiday date range applicable just to the AOG service (Y22:Y25) and added two additional shipping services called ‘Critical Sat’ and ‘Critical Sun’ each requiring a slightly different formula for calculating the delivery date. I have also added notes in row 10 and 25 to help explain which days and/or holidays should be included/excluded when calculating the delivery date for each of the shipping services.

Please re-read my original post for how the two scenarios need to be calculated. The example I gave for the AOG service showed that with an order date of Fri 18 Dec 2015 and a working day lead time of 3 should give a delivery date of Sat 26 Dec. This is calculated as follows:

3 working days from Fri 18 Dec takes you to Wed 23 Dec, then you need to add 2 calendar days transit time which would take you to Fri 25 Dec, but as this is a Holiday (within the range Y22:Y25), the date moves to the next day. As AOG deliveries can take place on a Saturday or Sunday the delivery date stays as Sat 26 Dec.

The important point to remember for all shipping services is that if the calculated delivery date falls on a certain day/holiday as defined by my notes in rows 10 and 25, then the date has to move forward by a day until such time as it reaches a day/date that is acceptable.

The formula in C8 (you copied across to originally F8) also isn’t quite right. Take a look at the ‘critical’ delivery service. Again using the example of an order date of Fri 18 Dec with a work day leadtime of 3, the delivery date should be Tues 29 Dec. This is calculated as follows:

3 working days from Fri 18 Dec takes you to Wed 23 Dec, then you need to day 3 calendar days transit time which would take you to Sat 26 Dec, but as this is a Saturday, the date moves to the next working day (i.e. Monday). As the Monday is also a holiday (in range Z22:Z40), it moves to the next working day which is Tuesday 29 Dec.

For a ‘routine’ delivery service where the order date is Fri 18 Dec with a working day leadtime of 3, the delivery date should be Wed 30 Dec. This is calculated as follows:

3 working days from Fri 18 Dec takes you to Wed 23 Dec, then you need to day 7 calendar days transit time which would take you to Wed 30 Dec. As Wednesday isn’t an excluded day nor is it a holiday, the delivery date stays as Wed 30 Dec.

Does this help? The same principles need applying to the delivery date for the ‘critical sat’ and ‘critical sun’ delivery services. The only difference is what day/date is considered included/excluded as per my notes in rows 10 and 25.

Hopefully you haven’t lost the plot yet. :wacko:

Thank you.

Mark

Reply | Quote

Hi Everyone,

I am still struggling with what formula/User Defined Function to use to solve this issue.

Attached to this message is a clean copy of the spreadsheet if anyone fancies a go.

There are 7 shipping services and each has its own transit time (measured in calendar days) to move the goods from the USA to the UK. Each of the shipping services has various restrictions on what day/date the delivery date can actually fall on. For ease I’ve detailed the criteria of what days/dates can and cannot be included against each service in rows 10 and 25. For example for an AOG shipping service, the delivery date can occur on any day of the week but not on a holiday within the range of Y22:Y25.

The formula I’m looking for in row 8 needs to comprise of 2 parts:

1. Order Date (B3) + Working Day Lead Time (B4) = X
This part can use the normal workday function

2. X + Transit Time (B7) = Y (delivery date)
All days between ‘X’ and B7 are to be counted including weekends and/or holidays BUT there are restrictions on which days/dates ‘Y’ can fall on.

Here are a few examples which hopefully will better explain how the delivery date needs to be calculated.

Example 1 – AOG Service

Order Date = 18 Dec 2015
Working Day Lead Time = 3
Calendar Day Lead Time = 5 (as the 19th and 20th is a weekend)
Delivery Date = Saturday 26 Dec 2015

With an order date of Fri 18 Dec + 3 working days takes you to Wed 23 Dec, then you need to add 2 calendar days transit time which would take you to Fri 25 Dec, but as this is a Holiday (within the range Y22:Y25), the date moves to the next day. As AOG deliveries can take place on a Saturday or Sunday the delivery date stays as Sat 26 Dec.

The important point to remember for all shipping services is that if the calculated delivery date falls on a certain day/holiday as defined by my notes in rows 10 and 25, then the date has to move forward by a day until such time as it reaches a day/date that is acceptable.

Example 2 – Critical Service

Order Date = 18 Dec 2015
Working Day Lead Time = 3
Calendar Day Lead Time = 5 (as the 19th and 20th is a weekend)
Delivery Date = Tuesday 29 Dec 2015

With an order date of Fri 18 Dec + 3 working days takes you to Wed 23 Dec, then you need to add 3 calendar days transit time which would take you to Sat 26 Dec, but as this is a Saturday, the date moves to the next working day (i.e. Monday). As the Monday is also a holiday (in range Z22:Z40), it moves to the next working day which is Tuesday 29 Dec.

Example 3 – Routine Service

Order Date = 18 Dec 2015
Working Day Lead Time = 3
Calendar Day Lead Time = 5 (as the 19th and 20th is a weekend)
Delivery Date = Wednesday 30 Dec 2015

With an order date of Fri 18 Dec + 3 working days takes you to Wed 23 Dec, then you need to add 7 calendar days transit time which would take you to Wed 30 Dec. As Wednesday isn’t an excluded day nor is it a holiday, the delivery date stays as Wed 30 Dec.

If anyone has any suggestions, I’d be really grateful.

Thanks,
Mark

42969-DeliveryDateCalc

Reply | Quote

Mark,

Thank you for the clarification on the delivery calculations. It is much clearer now. To make the code more readable, I created 2 named ranges for the holiday groupings:

holiday1 = Y22:Y25
holiday2 = Z22:Z40

You will need to do this also.

For simplicity in entering the formulas, I have created a user defined function (UDF) for each shipping service after its name. They all have the same parameters (order_date, workday_lead, transit_time):

WorkDay Lead Times
AOG_W(order_date, workday_lead, transit_time)
IOR_W(order_date, workday_lead, transit_time)
Critical_W(order_date, workday_lead, transit_time)
Critical_Sat_W(order_date, workday_lead, transit_time)
Critical_Sun_W(order_date, workday_lead, transit_time)
Routine_W(order_date, workday_lead, transit_time)
SFR_W(order_date, workday_lead, transit_time)

Calendar Lead Times
AOG_C(order_date, workday_lead, transit_time)
IOR_C(order_date, workday_lead, transit_time)
Critical_C(order_date, workday_lead, transit_time)
Critical_Sat_C(order_date, workday_lead, transit_time)
Critical_Sun_C(order_date, workday_lead, transit_time)
Routine_C(order_date, workday_lead, transit_time)
SFR_C(order_date, workday_lead, transit_time)

Enter the formula based on the shipping Service and the lead time method.

Example1: Cell B8 =AOG_W(B3,B4,B7)
Example2: Cell B23 =AOG_C(B18,B19,B22)

42975-Delivery1

HTH,
Maud

Reply | Quote

Maud,

Thank you very much for taking the time to look at this again. I will review the new file you’ve posted and come back to you after Christmas.

Many thanks,
Mark

Reply | Quote

Maud,

Happy New Year!

Your solution with the user defined functions and the named ranges for the holiday groupings is brilliant! Thank you very much. I really appreciate the time you’ve spent on this to come up with a solution. I have put the spreadsheet through quite a few simulations, and the results match 100% with the detail I gave you.

Would it be possible for you to make one (hopefully minor) modification please? This is to the ‘U.S. suppliers who quote working day lead times’ section.

The modification is to the first part of the formula in row 8:

Instead of

Order Date (B3) + Working Day Leadtime (B4) = X

Use

Order Date (B3) + Calendar Days Leadtime (B9) = X

Cell B9 will use the regular workday function and use a new date range of USA public holidays AA22:AA43. This will give me the added functionality that USA holidays will not be counted when adding a working day leadtime to a date.

The second part of the formula remains unchanged as follows:

X + Transit Time (B7) = Y (delivery date)
All days between ‘X’ and B7 are to be counted including weekends and/or holidays BUT there are restrictions on which days/dates ‘Y’ can fall on.

Example 1 – AOG Service

(B3) Order Date = 18 Dec 2015
(B4) Working Day Lead Time = 5
(B9) Calendar Day Lead Time = 10 (as the 19th and 20th is a weekend, 25th is a holiday, 26th and 27th is a weekend)
(B8) Delivery Date = Wed 30 Dec 2015

With an order date of Fri 18 Dec + 5 working days takes you to Mon 28 Dec, then you need to add 2 calendar days transit time which would take you to Wed 30 Dec.

The holiday date ranges ‘holiday1’ and ‘holiday2’ should continue to be utilised by the second part of the formula and continue to work exactly as you programmed them.

If this will cause too much work, please say and I’ll make do with the spreadsheet you’ve already done. I’m sorry I didn’t specify this from the outset.

Thank you in advance.

Reply | Quote