Formula to get the last Monday in February

[YasserKhalil]

Hello
I need a formula that returns the date of the last Monday in February for any year
For example I have the date in A1
01/01/2014
I need the last Monday For the year 2014

 

[YasserKhalil]

Apologies. I set for Fridays, not Mondays.
'Sides, VOG's formula is much simpler.
Excel 2010

	A	B	C
1	01/01/2013	Mon 25-Feb-13	Mon 25-Feb-13
2	01/01/2015	Mon 23-Feb-15	Mon 23-Feb-15
3	01/01/2016	Mon 29-Feb-16	Mon 29-Feb-16
4	01/01/2012	Mon 27-Feb-12	Mon 27-Feb-12

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B1 =DATE(YEAR(A1),3,1)-WEEKDAY(DATE(YEAR(A1),3,6)) <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula C1 {=MAX((MONTH(DATE(YEAR(A1),2,{21,22,23,24,25,26,27,28,29}))=2)*(WEEKDAY(DATE(YEAR(A1),2,{21,22,23,24,25,26,27,28,29}), 2)=1)*DATE(YEAR(A1),2,{21,22,23,24,25,26,27,28,29}))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

/AJ

The result is incorrect (24/2/2014) is not right....

 

[VoG]

According to Outlook and my diary the correct answer is 24/2/2014.

 

[adam087]

I'm getting 24-Feb with both formulae.

Excel 2010
	A	B	C
1	Ref Date	Vog's Formula	AJ's Formula
2	01/01/2012	Mon 27-Feb-12	Mon 27-02-12
3	01/01/2013	Mon 25-Feb-13	Mon 25-02-13
4	01/01/2014	Mon 24-Feb-14	Mon 24-02-14
5	01/01/2015	Mon 23-Feb-15	Mon 23-02-15
6	01/01/2016	Mon 29-Feb-16	Mon 29-02-16
7	01/01/2017	Mon 27-Feb-17	Mon 27-02-17
8	01/01/2018	Mon 26-Feb-18	Mon 26-02-18
9	01/01/2019	Mon 25-Feb-19	Mon 25-02-19
Sheet1

Cell Formulas
Range	Formula
A2	=DATE(2010+ROW(), 1, 1)
B2	=DATE(YEAR(A2),3,1)-WEEKDAY(DATE(YEAR(A2),3,6))
C2	{=MAX((MONTH(DATE(YEAR(A2),2,{21,22,23,24,25,26,27,28,29}))=2)*(WEEKDAY(DATE(YEAR(A2),2,{21,22,23,24,25,26,27,28,29}), 2)=1)*DATE(YEAR(A2),2,{21,22,23,24,25,26,27,28,29}))}
Press CTRL+SHIFT+ENTER to enter array formulas.

/AJ

 

[YasserKhalil]

Yes I'm so sorry for my fault
You are right.
The result is 24/2/2014

 

[Comfy]

The result is incorrect (24/2/2014) is not right....

Have you asked the right question?

As the 24th of Feb in 2014 is the last Monday.
Additionally the 23rd of Feb in 2015 is the last Monday.

Yes I'm so sorry for my fault

You are right.
The result is 24/2/2014

Nevermind.

 

[YasserKhalil]

=DATE(YEAR(A2);3;1)-WEEKDAY(DATE(YEAR(A2);3;6))

In this formula 3 refers to March .. What about 6 at the end of the formula?

 

[Comfy]

The 6th Day of MArch (3)

 

[VoG]

It takes the 1st of march thyen subtracts the weekday of the 6th of march. WEEKDAY counts Sunday as 1.

 

[YasserKhalil]

It takes the 1st of march thyen subtracts the weekday of the 6th of march. WEEKDAY counts Sunday as 1.

Thank you very much for great help
Your formula can be easily used with any month ..
What about if I want to change Monday with Thursday for example? Will I change the number 6 at the end of your formula?

 

[VoG]

Changing the 6 to 3 appears to give the correct result but I have not tested it thoroughly.