simply formula by writing it in VBA

Hi there,

I have written a series of formulas in an excel workbook and need to consolidate the steps.

What the formulas do is look up the value in B1 and if the date in cell "A1" is less than the first Tuesday of the month, then this cell should have First Tuesday of the month else it should have the first Tuesday of the next month.

Currently this is part of a really big spreadsheet so I would prefer it to only be the vlookup and one other cell.

I have attached the spreadsheet and would be very grateful if someone could suggest either some vba code to simplify the process or help me create a nested formula to consolidate all the steps.

thanks

Use the following formula:

=IF(AND(DAY(A1)<8,(DAY(A1)-WEEKDAY(A1)>0)),DATE(YEAR(A1),MONTH(A1),DAY(A1)-WEEKDAY(A1)+3),DATE(YEAR(EOMONTH(A1,0)+1),MONTH(EOMONTH(A1,0)+1),DAY(EOMONTH(A1,0)+1)-WEEKDAY(EOMONTH(A1,0)+1)+3))

Note: You require the addin Analysis Toolpak for this as I am using EOMONTH function

Breaking up the formula for readability

=IF(AND(DAY(A1)<8,(DAY(A1)-WEEKDAY(A1)>0)),DATE(YEAR(A1),MONTH(A1),DAY(A1)-WEEKDAY(A1)+3),
DATE(YEAR(EOMONTH(A1,0)+1),MONTH(EOMONTH(A1,0)+1),DAY(EOMONTH(A1,0)+1)-WEEKDAY(EOMONTH(A1,0)+1)+3))



- Mangesh

thank you! a great way of doing this!

Also note that, if you need any other day (other than tuesday), you just have to substitute the 3 in the formula with the weekday of the required day.

- Mangesh