Back-Dating to get an earlier date which excludes Holidays and Week-Offs

Originally Posted by DonkeyOte

I couldn't think of a good way to do this without having to double evaluate some part of the formula.... I also altered D:E such that E holds days one must offset should the result fall on the weekday specified in D (where 1 = Monday and 7 = Sunday)

Splendid !

But if I could just put the WeekDays like Tue,Wed,Thu instead of 1,2,3 etc...
I dont know how to explain that but can we avoid using the helper columns and numeric weekdays..

I am trying to use Name Defines

1. WeekOFF = Query!$D$2:$D$8
2. Holidays = Query!$C$2:$C$11

Can you please incorporate the Names in your formula and also by WeekDays like Tue,Wed,Thu and no extra helper column if possible..

The code does work but just a little personalisation...

Thanks lot...

Originally Posted by e4excel

Splendid !

But if I could just put the WeekDays like Tue,Wed,Thu instead of 1,2,3 etc...
I dont know how to explain that but can we avoid using the helper columns and numeric weekdays..

I am trying to use Name Defines

1. WeekOFF = Query!$D$2:$D$8
2. Holidays = Query!$C$2:$C$11

Can you please incorporate the Names in your formula and also by WeekDays like Tue,Wed,Thu and no extra helper column if possible..

The code does work but just a little personalisation...

Thanks lot...

Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER...

=LARGE(IF(ISNA(MATCH(F2-ROW(INDIRECT("1:10"))+1,C2:C11,0)),IF(ISNA(MATCH(TEXT(F2-ROW(INDIRECT("1:10"))+1,"ddd"),D2:D4,0)),F2-ROW(INDIRECT("1:10"))+1)),1)

Hope this helps!

ROW(INDIRECT("1:10")) returns the following array of values...

1
2
3
4
5
6
7
8
9
10

Therefore, assuming that F2 contains 3/24/2008, F2-ROW(INDIRECT("1:10"))+1 returns the following array of values...

3/24/08
3/23/08
3/22/08
3/21/08
3/20/08
3/19/08
3/18/08
3/17/08
3/16/08
3/15/08

Hope this helps!

Dear Domenic,

ROW(INDIRECT("1:10")) returns the following array of values...

Got it...Thanks but no particular reason to keep it till 10...it will not change the result if it were any other number.

Lets say If I had to hard-code only one Week-Off then how do i do that?
Like only single WeekOff="Sun"

[ =LARGE(IF(ISNA(MATCH(E2-ROW(INDIRECT("1:10"))+1,Holidays,0)),IF(ISNA(MATCH(TEXT(E2-ROW(INDIRECT("1:10"))+1,"ddd"),???,0)),E2-ROW(INDIRECT("1:10"))+1)),1) ]

I tried it but did not get the answer, the reason of coming to the basic thing is as different banks would have different WeekOffs both in terms of Days as well as Numbers.

In some earlier threads on queries on Dates, each day has some number assigned to it..Can that be used?

Originally Posted by e4excel

Dear Domenic,



Got it...Thanks but no particular reason to keep it till 10...it will not change the result if it were any other number.

Well, you'll need to make sure that a large enough array is created. For example, if F2 (or I guess E2 now) contains 3/21/08, D2:D4 contains Tue, Wed, and Thur, and each instance of 10 is replaced with 3, the formula will return #NUM!.

That's because E2-ROW(INDIRECT("1:3"))+1 creates the following array...

21-Mar-08
20-Mar-08
19-Mar-08

As you can see, none of the dates meets the criteria. The first date is a holiday and the next two are weekday-off's. As such, IF returns the following...

FALSE
FALSE
FALSE

Therefore, LARGE returns #NUM!.

I tried it but did not get the answer, the reason of coming to the basic thing is as different banks would have different WeekOffs both in terms of Days as well as Numbers.

In some earlier threads on queries on Dates, each day has some number assigned to it..Can that be used?

Can you post a small sample of data, along with the expected results?

Originally Posted by Domenic

Well, you'll need to make sure that a large enough array is created. For example, if F2 (or I guess E2 now) contains 3/21/08, D2:D4 contains Tue, Wed, and Thur, and each instance of 10 is replaced with 3, the formula will return #NUM!.

That's because E2-ROW(INDIRECT("1:3"))+1 creates the following array...

21-Mar-08
20-Mar-08
19-Mar-08

As you can see, none of the dates meets the criteria. The first date is a holiday and the next two are weekday-off's. As such, IF returns the following...

FALSE
FALSE
FALSE

Therefore, LARGE returns #NUM!.

Can you post a small sample of data, along with the expected results?

So what I understand is to keep it as large a number as possible instead of less...

I wish I could post a Sample but can right now as its not ready but I had the concept of picking the WeekOffs for different Banks..I need to gather the informatiion and tepmplatise that to use the magical formula...

But wanted to know how do i go about Hard-coding for a Bank with a single WeekOff like a Sunday or Saturday ( Only One) ...Hardcoded in the formula...

Dominic,

Your code works fabulously, there's no two minds about that..

I mentioned as I would have different template models in my mind and in one of my ideas..What If I wanted to only hard code it for one Single Day such as a "Sun" for a generic solution and and an OFFSET NAME define code for some other situation...

[ =LARGE(IF(ISNA(MATCH(E2-ROW(INDIRECT("1:10"))+1,Holidays,0)),IF(ISNA(MATCH(TEXT(E2-ROW(INDIRECT("1:10"))+1,"ddd"),"Sun",0)),E2-ROW(INDIRECT("1:10"))+1)),1) ]

Dear Domenic

Try replacing the reference with the following...

{"Thu"}

[ =LARGE(IF(ISNA(MATCH(E2-ROW(INDIRECT("1:10"))+1,Holidays,0)),IF(ISNA(MATCH(TEXT(E2-ROW(INDIRECT("1:10"))+1,"ddd"),"Thu",0)),E2-ROW(INDIRECT("1:10"))+1)),1) ]

Hope this helps!

No it doesnt work get a #NUM error...

Sorry my net speed is so slow that my message got posted after your solution but anyways wanted to tell you that i tried this both {[ = , <> ]} ...

Ex: E2= 9-Feb-2009 (Mon)

and 9-Feb-2009 is present in Holidays and there's only one weekoff which is Sunday ( 8-Feb-09) then the answer should be 7-Feb-09 Sat

=LARGE(IF(ISNA(MATCH(E2-ROW(INDIRECT("1:10"))+1,Holidays,0)),IF(TEXT(E2-ROW(INDIRECT("1:10"))+1,"ddd")<>"Sun",E2-ROW(INDIRECT("1:10"))+1)),1)

It gives the same answer 8-Feb-09 whereas the asnwer should be 7-Feb-09 Sat

The formula should return the desired result. Have a look at the attached file...

Hey Thanks a lot Domenic!

I got it finally...ahd to actually scrutinise both the codes to understand the difference one is with MATCH and the other without...

Thanks a million^million for hanging on till I got the solution....

You're very welcome!