[Solved] automate day calculator

[Stefan]

Is there a way to automate this formula that I use to calculate the date of a specific day in a month (2nd Tuesday, 4th Friday etc)

=$B$5-DAY(B5)+1+D5*7-WEEKDAY(B5-DAY(B5)+8-C5)

 

[Lutfor Rahman Shimanto]

Is there a way to automate this formula that I use to calculate the date of a specific day in a month (2nd Tuesday, 4th Friday etc)

=$B$5-DAY(B5)+1+D5*7-WEEKDAY(B5-DAY(B5)+8-C5)

Hello Stefan

Thanks for bringing the issue to ExcelDemy Forum. I have analyzed the formula you have provided. The formula calculates the date of a specific day in a month. You must provide a date, week, and nth day as inputs. You need three distinct inputs to use this formula. Moreover, you are not allowed to input week as Sat, Sun, Wed etc. To work with the formula you gave, you must input week as integers from 1 to 7.

However, you want a calculator that will take a date and other inputs like 2nd Tuesday, 4th Friday etc. I am delighted to inform you that I have implemented such a thing by modifying your formula. To reach your requirement, I have to consider two more columns responsible for assigning a numeric value to each weekday.

Excel Formula:

=$E$5-DAY($E$5)+1+LEFT($E$8,1)*7-WEEKDAY($E$5-DAY($E$5)+8-VLOOKUP(MID($E$8,FIND(" ",$E$8)+1,3),$B$5:$C$11,2,FALSE))

OUTPUT:

​

I have attached the Solution Workbook to help you understand better. Don't hesitate to contact us again if you have any more questions.

Regards
Lutfor Rahman Shimanto

 

[Stefan]

Is there a way to automate this formula that I use to calculate the date of a specific day in a month (2nd Tuesday, 4th Friday etc)

=$B$5-DAY(B5)+1+D5*7-WEEKDAY(B5-DAY(B5)+8-C5)

Hello Lutfor, That's perfect, thank you very much

 

[Lutfor Rahman Shimanto]

Hello Lutfor, That's perfect, thank you very much

You are most welcome, Stefan.

Regards
Lutfor Rahman Shimanto