# Formula to set date to nearest previous (most recent) 25th of the month

I need a couple things here, but I think they may be similar formulas:

I have a list of dates in an Excel file (Column A). I'd like to create a 2nd list (Column B) that will have a formula that will return the nearest previous (most recent) 25th day of the month of the corresponding cell in Column A.  If the date falls exactly on the 25th, I need the result to keep that same date.

So for example:

• If A1 has the date of 3/14/16, B1 will return 2/25/16.
• If A2 has the date of 5/28/16, B2 will return 5/25/16.
• If A3 has the date of 5/25/16, B3 will return 5/25/16.

I also need a formula that will return the next 25th of the month.

For example:

• If A1 has the date of 3/14/16, B1 will return 3/25/16.
• If A2 has the date of 5/28/16, B2 will return 6/25/16.
• If A3 has the date of 5/25/16, B3 will return 6/25/16.

``````=IF(DAY(A1)>25,DATE(YEAR(A1),MONTH(A1),25),DATE(YEAR(A1),MONTH(A1)-1,25))
``````

EDIT#1:

This will handle the case of the day being the 25th:

``````=IF(DAY(A1)=25,A1,IF(DAY(A1)>25,DATE(YEAR(A1),MONTH(A1),25),DATE(YEAR(A1),MONTH(A1)-1,25)))
``````
• Thanks so much Gary's Student. I ran some tests and I ran into an unforeseen issue: If the date falls exactly on the 25th, I need the result to keep that same date. So my original example should have read thus: If A1 has the date of 3/14/16, B2 will return 2/25/16. If A2 has the date of 5/28/16, B2 will return 5/25/16 If A3 has the date of 5/25/16, B3 will return 5/25/16 – D. Hill Dec 21 '16 at 1:49
• @D.Hill Would you like an update ?? – Gary's Student Dec 21 '16 at 1:51
• Yes, please. Can you help with a formula that will cover all three scenarios? Maybe some sort of nested IF statement? – D. Hill Dec 21 '16 at 2:00
• @D.Hill See my EDIT#1 – Gary's Student Dec 21 '16 at 2:04
• Hey thanks! I had to tweak your formula a tiny bit. I replaced "TODAY()" with (A1), because I didn't want it to return today's date if A1 fell on the 25th. I wanted it to remain the same as A1. So this is what I ended up with. `=IF(DAY(A1)=25,(A1),IF(DAY(A1)>25,DATE(YEAR(A1),MONTH(A1),25),DATE(YEAR(A1),MONTH(A1)-1,25)))`. It seems to work, so thanks again so much! – D. Hill Dec 21 '16 at 2:13

I would like to suggest one single Formula with little modification, will produce the nearest previous 25th of the month. Whether date is 25th or Greater than 25th or less than 25th.

My Formula is:

``````=IF(OR(DAY(A98)=25,DAY(A98)>25),DATE(YEAR(A98),MONTH(A98),25),DATE(YEAR(A98),MONTH(A98)-1,25))
``````

NB: My Data Range is A98, you can change it as you need.

• Write your concern,, why this has been down voted! – Rajesh S Jun 6 '19 at 13:13

For the second issue use this: `=IF(DAY(G1)=25,(G1),EOMONTH(TODAY(),0)+25)`, where `G1` is a cell that has today's date. I tried this (as I have a friend who has a payment due on the 25th of each month, incidentally, and we're trying to see how many payments she has left) and it works.

• (1) Why would you want to use `TODAY()` and a cell that contains today’s date?  Given that the question doesn’t say anything about “today”, and that the OP rejected the use of `TODAY()` (in a comment), you should probably just say `=IF(DAY(G1)=25,G1,EOMONTH(G1,0)+25)` (note that I deleted an unnecessary set of parentheses).  (2) The question uses the example of the input data being in cell `A1`.  It’s nice to use the OP’s conventions when you answer; e.g., use `A1` instead of `G1`. … (Cont’d) – Scott May 26 '19 at 2:12
• (Cont’d) …  (3) But, more importantly, the requirement (in this question) is to determine the next 25th of the month, not the 25th of the next month. The question explicitly gives the example 3/14/16 → 3/25/16, but this answer gives 4/25/16 (the 25th of the next month). – Scott May 26 '19 at 2:12