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.

 

[Rainmanne]

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}))

This one works! Thanks a lot!

 

[Rainmanne]

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

Date EOM
23-02-2018 22-03-2018
24-03-2018 24-04-2018
22-04-2018 24-04-2018
21-05-2018 24-05-2018
19-06-2018 24-06-2018
18-07-2018 24-07-2018
16-08-2018 23-08-2018

 

[Rainmanne]

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}))

I’ve been trying to decipher the formula above to use it for getting the beginning of the month as well.
As I understand it works this way:
1. If TODAY > end of the current month we have array {0,1} for the SUMPRODUCT formulas, if it is < end of the current month, we have {1,0}.
2. So in the EOMONTH formula we have another array {-1,0}, so in the SUMPRODUCT formula:

• {0,1} and {-1,0} give us 0: end of the current month + 24 – adjustments
• {1,0} and {-1,0} give us -1: end of the previous month + 24 – adjustments

So I tried to adjust the formula for the beginning of the month using end of the current month as reference point:

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

1. If TODAY > end of the current month we have array {0.5,0} for the SUMPRODUCT formulas, if it is < end of the current month, we have {1,0}.
2. So in the EOMONTH formula we have another array {-1,0}, so in the SUMPRODUCT formula:

• {0.5,0} and {-2,0} should give us -1: end of the previous month + 25 – adjustments
• {1,0} and {-2,0} should give us -2: end of the month before the previous + 25 – adjustments

The second part works. For example, for 01/02/2018 I gave 25/01/2018 as a beginning of the month and so for all other first of the month dates.
However, the second part does not work for me. For example for 28/02/2018 I get 12/01/1959, for 31/03/2018 I get 27/01/1959 and so on. I guess I am doing something wrong in arrays. I wonder what my mistake is.

 

[Tetra201]

In brief, here is how it works:

The EOMONTH(B2,{-1,0})+24-LOOKUP(WEEKDAY(EOMONTH(B2,{-1,0})+25,16),{1;2;3},{1;2;0}) part generates an array of two dates.

For today (2018-03-14), they are 2018-03-22 and 2018-04-24.

Now, we need to select just one of them based on the (B2>...) condition.

The selection can be done by multiplying the two dates by {0,1} or {1,0} and summing the results.

For today, this is equivalent to: ="2018-03-22"*1+"2018-04-24"*0

Alternatively, the selection can be done by using the INDEX function. The formula becomes:

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

Hope this explains.

 

[Rainmanne]

In brief, here is how it works:

The EOMONTH(B2,{-1,0})+24-LOOKUP(WEEKDAY(EOMONTH(B2,{-1,0})+25,16),{1;2;3},{1;2;0}) part generates an array of two dates.

For today (2018-03-14), they are 2018-03-22 and 2018-04-24.

Now, we need to select just one of them based on the (B2>...) condition.

The selection can be done by multiplying the two dates by {0,1} or {1,0} and summing the results.

For today, this is equivalent to: ="2018-03-22"*1+"2018-04-24"*0

Alternatively, the selection can be done by using the INDEX function. The formula becomes:

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

Hope this explains.

Thanks a lot! Now I've got it. It works perfectly.