Date based on day convention

liquid

New Member
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.

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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).

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

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

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

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

Replies
4
Views
676
Replies
5
Views
182
Replies
5
Views
628
Replies
2
Views
422
Replies
15
Views
958

1,219,895
Messages
6,150,830
Members
450,987
Latest member
PopeScooby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back