Date based on day convention

liquid

New Member
Joined
Sep 8, 2004
Messages
48
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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Well-known Member
Joined
Feb 28, 2003
Messages
2,615
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

New Member
Joined
Sep 8, 2004
Messages
48
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

New Member
Joined
Sep 8, 2004
Messages
48
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
 

Forum statistics

Threads
1,136,309
Messages
5,674,998
Members
419,541
Latest member
freddyboots

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top