Date related problem concerning weekends

[Bodz]

Hi All,

Hope someone can help.
I have a date related problem and it goes like this:-
Within any working week a TRD (target ready date) is set by customer services.
Depending on the product selected (21;22;23;24) each has a lead time respectively of (2days;5days;7days and 9days)
Initially I thought it would be simple enough to take the TRD and subtract 2 days from it which would then give me a start date.
This works quite well until you realise that the new start date could fall on a Saturday or Sunday which I can't allow to happen.

What I require is a formula that looks at the product code and then subtracts the equivalent amount of days from the TRD and also takes into account weekends. If a weekend falls within thes period, then I would like the start date to be on the Friday.

Regards

Bodz

[DonkeyOte]

You don't specify your XL Version in your profile but I would suggest reviewing the WORKDAY Function... pre XL2007 this requires activation of the Analysis ToolPak (via Tools -> Add-Ins menu).

[Bodz]

Hi DonkeyOte,

XL version is 2003.
I've not worked with WORKDAY function before - any tips.
So far, I've been trying to use the NETWORKDAYS function.

Bodz

[DonkeyOte]

WORKDAY like NETWORKDAYS by default ignores weekends when calculating and has optional public holidays parameter which is obviously important in these scenarios... the basic premise ?

=WORKDAY(date,adjustment,[holidays])

the adjustment can be negative... so in your case

=WORKDAY(A1,LOOKUP(B1,{21,22,23,24},{-2,-5,-7,-9}),_holidays)

where:

A1: cell containing TR Date
B1: Product code (21-24)
_holidays: named range containing the public holiday dates to be ignored in addition to weekends.

you can adjust the adjustment array to account for whether or not current TRDate itself is inclusive of lead time (ie -2 becomes -1 etc...) ... we're also assuming that the target delivery date is always a valid workday.

[Bodz]

Hi DonkeyOte,

Thanks for the reply. Sorry I didn't get back earlier.
I had a ply withthe function and you were right. It was the missing bit that I needed to make the rest work.

Thanks for your help.

Bodz

[Bodz]

Now that the problem is solved I need to mark it as such but am having a bit of a problem finding where to go in order to do this.

Bodz

[DonkeyOte]

see FAQ / How To ?