Formula to get the last Monday in February

C

Comfy

Well-known Member

Joined

Dec 21, 2009

Messages

3,386

• Oct 10, 2014

• #2

YasserKhalil said:

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

Click to expand...

Just to clarify, you want the Last Monday in February for the year you specify or,

The last Monday of the Year you specify?

Your question asks for both.

 

Upvote 0

Y

YasserKhalil

Well-known Member

Joined

Jun 24, 2010

Messages

852

• Oct 10, 2014

• #3

The last Monday of Feb. for any year

You know that Feb may be 28 days or 29 days

 

Upvote 0

adam087

Well-known Member

Joined

Jun 7, 2010

Messages

1,356

• Oct 10, 2014

• #4

Perhaps try this...
Excel 2010

	A
1	01/12/2013
2	Fri 22-Feb-13

<colgroup><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula A2 {=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)=5)*DATE(YEAR(A1),2,{21,22,23,24,25,26,27,28,29}))} <thead> </thead><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>

Hope that helps.

/AJ

 

Upvote 0

C

Comfy

Well-known Member

Joined

Dec 21, 2009

Messages

3,386

• Oct 10, 2014

• #5

YasserKhalil said:

The last Monday of Feb. for any year

You know that Feb may be 28 days or 29 days

Click to expand...

I do, it's just this:

YasserKhalil said:

I need a formula that returns the date of the last Monday in February for any year

Click to expand...

Is a different question to this:

YasserKhalil said:

I need the last Monday For the year 2014

Click to expand...

 

Upvote 0

VoG

Legend

Joined

Jun 19, 2002

Messages

63,650

• Oct 10, 2014

• #6

Try

=DATE(YEAR(A1),3,1)-WEEKDAY(DATE(YEAR(A1),3,6))

 

Upvote 0

C

Comfy

Well-known Member

Joined

Dec 21, 2009

Messages

3,386

• Oct 10, 2014

• #7

Answered already.

 

Upvote 0

adam087

Well-known Member

Joined

Jun 7, 2010

Messages

1,356

• Oct 10, 2014

• #8

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

<colgroup><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B1 =DATE(YEAR(A1),3,1)-WEEKDAY(DATE(YEAR(A1),3,6)) <thead> </thead><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}))} <thead> </thead><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

 

Upvote 0

Y

YasserKhalil

Well-known Member

Joined

Jun 24, 2010

Messages

852

• Oct 10, 2014

• #9

Thanks sir but the result is not correct
The last Monday of February for example 2014 is 25/2/2014
and the result of your formula is 28/2/2014

 

Upvote 0

VoG

Legend

Joined

Jun 19, 2002

Messages

63,650

• Oct 10, 2014

• #10

With the formula I posted

Excel Workbook
	A	B	C
1	01/01/2014		24/02/2014
Sheet6

 

Upvote 0