Moving end of the month

[Rainmanne]

I am trying to get a formula for the month's end on any particular day (TODAY) but can't get my head around it.

Basically, end of the month is 24th, unless 25th falls on the weekend. So if the 25th is Saturday, EOM is 23rd and if it is Sunday, then 22nd.

I can build an IF structure for the date, it is not difficult:

=IF(WEEKDAY(DATE(YEAR(B2),MONTH(B2),25),2)=6,DATE(YEAR(B2),MONTH(B2),23),IF(WEEKDAY(DATE(YEAR(B2),MONTH(B2),25),2)=7,DATE(YEAR(B2),MONTH(B2),22),DATE(YEAR(B2),MONTH(B2),24)))

Where B2 is TODAY(). Probably not very elegant, but it works.

However, I cannot figure out how to change a month, because the end date is different in each particular month. For example in February this year, end of the month should be 22/02/2018 up to this date and 22/03/2018 from 23/02/2018 till 22/03/2018. After that it should change to 24/04/2018. But I cannot figure out how to make the change.

 

[baitmaster]

I think you want to use
=DATE(YEAR(B2),IF(DAY(B2)>24,1,0)+MONTH(B2),24)-MAX(WEEKDAY(DATE(YEAR(B2),IF(DAY(B2)>24,1,0)+MONTH(B2),24)-1)-5,0)

Where B2 is any date

 

[Rainmanne]

I think you want to use
=DATE(YEAR(B2),IF(DAY(B2)>24,1,0)+MONTH(B2),24)-MAX(WEEKDAY(DATE(YEAR(B2),IF(DAY(B2)>24,1,0)+MONTH(B2),24)-1)-5,0)

Where B2 is any date

Thanks a lot. Unfortunately it does not handle the date and teh change of the month properly. For example, on 21/02/2016 the end of the month should be 22/02/2018 as Feb 25 is Sunday, but the formula shows 23/02/2018. On 23/02/2018 and beyond it should be 22/03/2018 (again March 25 is Sunday) but it shows 23/02/2018 for 22nd and 23rd of Feb and 23/03/2018 for the rest of the month.

 

[kvsrinivasamurthy]

If 24 th is Saturday or Sunday What is EOM date.

 

[kvsrinivasamurthy]

Try this

=DATE(YEAR(B2),MONTH(B2),24)-IF(WEEKDAY(DATE(YEAR(B2),MONTH(B2),24),2)=5,1,IF(WEEKDAY(DATE(YEAR(B2),MONTH(B2),24),2)=6,2,0))

[TABLE="width: 190"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]EOM[/TD]
[/TR]
[TR]
[TD="align: right"]23-02-2018[/TD]
[TD="align: right"]22-02-2018[/TD]
[/TR]
[TR]
[TD="align: right"]24-03-2018[/TD]
[TD="align: right"]22-03-2018[/TD]
[/TR]
[TR]
[TD="align: right"]22-04-2018[/TD]
[TD="align: right"]24-04-2018[/TD]
[/TR]
[TR]
[TD="align: right"]21-05-2018[/TD]
[TD="align: right"]24-05-2018[/TD]
[/TR]
[TR]
[TD="align: right"]19-06-2018[/TD]
[TD="align: right"]24-06-2018[/TD]
[/TR]
[TR]
[TD="align: right"]18-07-2018[/TD]
[TD="align: right"]24-07-2018[/TD]
[/TR]
[TR]
[TD="align: right"]16-08-2018[/TD]
[TD="align: right"]23-08-2018[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rainmanne]

Sorry, let me clarify:

Let's take March. 25th is Sunday, so the last day of the month should be 22nd. The new month starts on 23rd then. In April, 25th is Wednesday, so the last day of the month should be 24th.

TODAY EOM
20/03/2018 22/03/2018
21/03/2018 22/03/2018
22/03/2018 22/03/2018
23/03/2018 24/04/2018
24/03/2018 24/04/2018

 

[kvsrinivasamurthy]

Pl see my post .Same result is there.

 

[Rainmanne]

This is what I have after plugging in the formula from the last post:

TODAY EOM
20/02/2018 22/02/2018
21/02/2018 22/02/2018
22/02/2018 22/02/2018
23/02/2018 22/02/2018
24/02/2018 22/02/2018
25/02/2018 22/02/2018
26/02/2018 22/02/2018
27/02/2018 22/02/2018
28/02/2018 22/02/2018
01/03/2018 22/03/2018

 

[Tetra201]

See if this works for you:

=SUMPRODUCT(IF(B2>EOMONTH(B2,-1)+24-LOOKUP(WEEKDAY(EOMONTH(B2,-1)+25,16),{1;2;3},{1;2;0}),{0,1},{1,0}),EOMONTH(B2,{-1,0})+24-LOOKUP(WEEKDAY(EOMONTH(B2,{-1,0})+25,16),{1;2;3},{1;2;0}))

 

[kvsrinivasamurthy]

What is the Expected result for the table in my previous post.