Find fourth Thursday each month and if NASDAQ closed that day, take next day

0
votes

I am working in Excel where I need to find the fourth Thursday in each month but if on that day NASDAQ is closed take the day after. I have this formula for each fourth Thursday: 

=DATE(YEAR(A2),MONTH(A2),CHOOSE(WEEKDAY(DATE(YEAR(A2),MONTH(A2),1)),26,25,24,23,22,28,27))

but I am not be to get the next business day where the fourth Thursday is any one of: 

01-Jan-14
20-Jan-14
17-Feb-14
18-Apr-14
26-May-14
03-Jul-14
04-Jul-14
01-Sep-14
27-Nov-14
28-Nov-14
24-Dec-14
25-Dec-14
1
exceldatetimeif-statementexcel-formulaworksheet-function
it'd make for an ugly formula, but couldn't you wrap it in an IF statement, and if it's blank (or an error) calculate the next business day? – sous2817
That's what I was thinking sous2817 – jswan
i was trying that. it didnt get the required result.. – Manya Mohan
If 4th Thursday is a trading holiday then it is not present. for ex. 22-Dec-14 23-Dec-14 29-Dec-14 30-Dec-14 31-Dec-14 – Manya Mohan
Can you post the series? – Excel Hero

1 Answers

0
votes

Please try appending to your formula: 

+IF(OR(A2=41823,A2=41970,A2=41998),1,0)