Date formula not working as intended

[suciulaurentiu]

Hello,
I have the following question regarding the formula and what I am trying to achieve:

Start_Date , End_date
months_interval = one number of random months (ex. 48)

Start_Date - Today (ex. 01.11.2021)
End_date - =IFERROR(EDATE((EDATE(Start_date;months_interval))-1;1);""))

I am trying here to calculate the end date by adding some months interval and the day should -1 day of the result ( we should have to count even for the february months, if it's a leap year or not...)

Ex: Start : 01.12.2021 End: 31.12.2023
Start: 05.12.2021 End: 04.01.2024

Right now my end result is: Start : 01.12.2021 End: 30.12.2023 not sure what is wrong..

 

[suciulaurentiu]

You didn't explain how 30/1 data can be correct if the 31/1 results (which are all one day later) are wrong.

I can't see how any of the dates you just posted make sense for the intervals stated.

if you use my formula > =(DATE(YEAR( date of today, in this case 31.01.2022 );MONTH( date of today )+(months interval which in this case is 36 );DAY( date of today )-1))) you will see that for 30/1 results are correct and for the 31 are incorrect. This is the anomaly i am trying to explain myself through excel..

 

[RoryA]

Yes, but what makes them incorrect? If your start days are one day apart, and the results are one day apart, why is one set of results wrong by your logic while the other is not?

Note: I'm not asking why the formula does what it does, I'm asking what is wrong with the results. You appear to be saying that the results for 31/1 and 30/1 should be the same. If so, why? If not, what are you saying?

 

[suciulaurentiu]

Yes, but what makes them incorrect? If your start days are one day apart, and the results are one day apart, why is one set of results wrong by your logic while the other is not?

Note: I'm not asking why the formula does what it does, I'm asking what is wrong with the results. You appear to be saying that the results for 31/1 and 30/1 should be the same. If so, why? If not, what are you saying?

That's what i am trying to explain, i write the formula, i have start date today, i insert parameter 36 months, and i get the wrong results. If i type in any other day than 31.01.2022, i get correct results. Not sure how to explain better. If you like i can provide an excel table. Kind regards,

 

[RoryA]

Please provide just two examples for the same month parameter - one for start date 30/1/2022 and one for 31/1/2022 and explain:
-what the results are
-what they should be

then we'll see if we can go from there.

 

[suciulaurentiu]

Please provide just two examples for the same month parameter - one for start date 30/1/2022 and one for 31/1/2022 and explain:
-what the results are
-what they should be

then we'll see if we can go from there.

Here are the results for 30.01.2022 and they are correct by my formula.

Today
30.01.2022	24months	29.02.2024
	36months	01.03.2025
	48months	01.03.2026
	60months	01.03.2027
	72months	29.02.2028
	84months	01.03.2029

And here are the results by the same formula for 31.01.2022 and they are wrong:

Today
31.01.2022	24months	01.03.2024
	36months	02.03.2025
	48months	02.03.2026
	60months	02.03.2027
	72months	01.03.2028
	84months	02.03.2029

 

[RoryA]

And what should the results for 31/1/2022 be? Given that the start day is one day later than 30/1/2022 why should the result dates not be one day later than those produced for 30/1?

 

[suciulaurentiu]

And what should the results for 31/1/2022 be? Given that the start day is one day later than 30/1/2022 why should the result dates not be one day later than those produced for 30/1?

The correct results should be : (which is a day earlier than calculated by the excel).

29.02.2024
01.03.2025
01.03.2026
01.03.2027
29.02.2028
01.03.2029

 

[RoryA]

OK, so why should the results be the same as for 30/1/2022? And why does this only seem to apply to that particular date?