Date based on day convention

[liquid]

I am trying to derive a date value (in dd/mmm/yy format) based on a given rule.

I want the dates to appear quarterly (so there are only four of these dates per year - Mar, Jun, Spe and Dec).

The rule is as follows:

Two business days before the third Wednesday of the month (the month as above).

I have set my spreadsheet up so that the year is in column Bstarting in row 4. So 2006 runs from B4:B7, 2007 from B8:B11, 2008 B12:B15 etc.

I then have the month in column C. So C4 is Mar, C5 is Jun, C6 is Sep, C7 is Dec, then C8 is Mar, C9 is Jun etc etc.

I want column D to return the correct date for each quarter.

So for example. D6 will return 19-Sep-06. This is two business days before the third Wednesday of September 2006.

Can anybody help me with a generic formula to work this out for each quarter??

Thanks a lot.

 

[Andrew Poulsom]

In D4 copied down:

=WORKDAY(DATE(B4,MONTH(TEXT("01"&C4&B4,"ddmmmyyyy")),1+((3-(4>=WEEKDAY(DATE(B4,MONTH(TEXT("01"&C4&B4,"ddmmmyyyy")),1))))*7)+(4-WEEKDAY(DATE(B4,MONTH(TEXT("01"&C4&B4,"ddmmmyyyy")),1)))),-2)

For me Sep 2006 was Monday 18th (not Tue 19th).

 

[Krishnakumar]

Hi,

=IF(WEEKDAY(DATE(B4,MONTH("1"&C4),1))<5,(DATE(B4,MONTH("1"&C4),1)-WEEKDAY(DATE(B4,MONTH("1"&C4),1)))+16,(DATE(B4,MONTH("1"&C4),1)-WEEKDAY(DATE(B4,MONTH("1"&C4),1)))+23)

HTH

 

[liquid]

thanks a lot guys, both work plerfectly. Yes, Andrew you're right. I'm working with a couple of different rules (i'm now going to adapt the formula for the other rule) and was getting them mixed up in my description!!

Cheers

 

[barry houdini]

If you want to exclude weekends but not holidays you can use this in D4 copied down

=(19&"-"&C4&"-"&B4)-WEEKDAY(19&"-"&C4&"-"&B4,3)

To exclude holidays also.....

=WORKDAY((21&"-"&C4&"-"&B4)-WEEKDAY(19&"-"&C4&"-"&B4,3),-2,Z$1:Z$10)

where Z1:Z10 contains your holiday dates

in both cases format as date

 

[liquid]

wow - that one is clever! I won't pretend to understnad the logic, but it certainly works.

Haven't got a library of business holidays for the next few years so will stick to the first one just now, but might use the holiday version later.

Thanks