Date calculation...

[bbbb1234]

Since Outlook will not do what I need using the recurring feature, I thought I would use Excel to generate the dates, and then import the date list into my Outlook calendar. Here is the description.
I need to get the date of the Monday before each first Tuesday of the following month. There is a tricky border condition that I can't figure out so I will provide a few examples:
- For May 2022: the first Tuesday of May falls on 5/3/22 so the last Monday of the previous month is 4/25/22 and it happens to be a full week in April
- For June 2022: the first Tuesday of June falls on 6/7/22 so the last Monday of the previous month is 5/30/22 but it is not a full week in May

My setup is:
In Column A I have a date with the first day of each month like this:
11/1/22 In Column B, I want the formula that will give me date in November of the Monday in November before the first Tuesday of December
12/1/22 In Column B, I want the formula that will give me date iin December of the Monday in December before the first Tuesday of January
1/1/23 In Column B, I want the formula that will give me date in January of the Monday in January before the first Tuesday of February
2/1/23 In Column B, I want the formula that will give me date in February of the Monday in February before the first Tuesday of March
etc.

Thanks in advance for the assist!!!

 

[Leo Skywalker]

Hi there,

If your column with the first days of each month starts in A2, then:

B2

=EOMONTH(A2,0)+1-WEEKDAY(EOMONTH(A2,0)+1,12)

Copy down.

 

[Habtest]

Hi, hope I'm correct by interpreting the request as finding the date of last Monday in the month. Try:

Book1
	A	B
1	2022/4/1	2022/4/25
2	2022/5/1	2022/5/30
3
4	2022/11/1	2022/11/28
5	2022/12/1	2022/12/26
6	2023/1/1	2023/1/30
7	2023/2/1	2023/2/27
Sheet1

Cell Formulas
Range	Formula
B4:B7,B1:B2	B1	=EOMONTH(A1,0)-WEEKDAY(EOMONTH(A1,0),2)+1
A2,A5:A7	A2	=EOMONTH(A1,0)+1

 

[bbbb1234]

Thanks Leo Skywalker and Habtest for the formulas! I think I have it sorted out now!!!

 

[Leo Skywalker]

Glad We could help.
Please mark a response as a solution.