Replies

Thank you.

My colleague has just clarified that some of the data on the spreadsheet is extracted from his stock system. Crucially the date fields I’m having problems with are actually coming from his system. When he views the Excel file, he just sees the date in the cell (in the format MM/DD/YYYY). I don’t think this helps us though does it?

Very odd! Is there any way I can extract the date element out of the formula?

Good question. No I didn’t create them. I received the spreadsheet exactly as you can see it. For the purposes of this forum I’ve just removed the rest of the columns we have no interest in. It is something Excel is doing itself.

Sorry, you misunderstand. My colleague IS directly typing the date in his end directly as the date.

Thank you. In addition to the formula Hans provided above that worked off a working day value, I also need a formula (in B17) to work off a calendar day value (B15) as per the attached spreadsheet. I suspect Hans’ formula just needs a slight modification but I can’t figure this out. For example todays date is Tues 9 Sept. Calendar days lead time is 5 (but this falls on a Sunday). Delivery service chosen is ‘Standard -2 working days’. So the delivery date should equal Wed 17 Sept. (This is because the supplier won’t hand over the goods to the courier until the Monday 15th). Thanks again.

Ok. Understood.

Hans,

This works perfectly. Unfortunately I need the Analysis ToolPak running to use the formula =WORKDAY(B3,B4,Z16:Z42)-B3 in cell B5 (Calendar Days Lead Time) on another worksheet. Unless you can suggest another way of calculating this? See attached. Thanks, Mark

No change.

Hans, I’m getting ‘#NAME?’ in cell B6.

The latest formula you’ve suggested does do what I expect SOME of the time. Attached is the spreadsheet which will make it easier to explain. The idea is for a user to enter an order date and a working day leadtime (if there is one). Excel calculates this into calendar days. The user then has to select a delivery service. I’ve then used cell B7 to calculate a temporary date so that further calculations can be carried out in cell B8. My idea was that row 7 would be hidden so the user only sees the final result in B8. I’m happy to scrap row 7 if one formula in B8 can achieve all of this. A delivery date must only ever occur on a Saturday if the user has selected a Saturday delivery in B6. Deliveries CANNOT ever take place on a Sunday or Bank Holiday. So if 2 working days delivery is selected this must count 2 working days AND take into account bank holidays (range Z18:Z44). Making the order date 22/08/2008 and then choosing your delivery service is a good way to see how the formula responds (as the 25th was a UK bank holiday). I accept that the spreadsheet has evolved a little since my posts yesterday! Thanks, Mark

Thank you Hans and Steve. I would never have figured that out! I’m still carrying out some testing but first impressions look very promising. I will post back to confirm one way or the other. Thanks again.

Following on from the formula you suggested above, how could this be amended to take into account the following:

1. If cell B6=”Saturday” (as text) and the date in B7 is a Saturday then don’t add anything. Otherwise perform the rest of the formula you have stated.

2. The formula also needs to take into account bank holidays. So if B7 = Saturday, add 3 days but if the Monday is a Bank Holiday, add 1 more day. If B7 = Sunday, add 2 days but if the Monday is a Bank Holiday, add 1 more day. If B7 isn’t a Saturday or Sunday, don’t add anything.

How would I structure my IF statement to cover these scenarios? I know I need to enter a range of dates that would be my Bank Holidays.

Really appreciate your advice again. Thanks.

Many thanks. Boy would I struggle without the fantastic support from Woody’s Lounge!

Thanks Steve. That works a treat.