Reference previous weekday to exclude holidays

[Jogier505]

I am trying to compare values from this week to last week with the exception that will overlook holidays. ie, from Monday to last Monday. But I am running into an issue if there is a holiday in the week as my formulas are looking for the value 5 days prior to the current day.

I think the sample will give you a clearer look at the problem.

Jim O

MOD: original attachment removed to avoid confusion - see latter post for correct file

[martindwilson]

er that workbook is about dropdowns not dates!

[DonkeyOte]

I'm with Martin but you might want to consider the WORKDAY function, ie:

Please Login or Register  to view this content.

that in red is optional but allows you to specify the range containing public holiday dates....
change A1 as required...

Note: pre XL2007 use of WORKDAY requires activation of Analysis ToolPak add-in via Tools -> Addins

[Jogier505]

Oops!! Sorry about that I hit the wrong key. Lets try that again.

Jim O

[DonkeyOte]

You could perhaps revert to something along the lines of:

Please Login or Register  to view this content.

or if you prefer a single formula which can be applied to J3:N4 you could use an Array, ie:

Please Login or Register  to view this content.

[Jogier505]

DonkeyOte,

Thanks. The lookup formula is just what I was looking for.

Again, sorry for the senior moment with the wrong worksheet, mabe I was blinded by the new bright screen!

Jim O