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]

If you just want the end of the relevant month, why can't you just use EOMONTH and not subtract 1?

That's because I don't want the end of the month..I calculate it all, reporting to ()TODAY . Today being 31.01.2022 is just a coincidence, but i might have all days of the month for a start date from 01 till 31

 

[suciulaurentiu]

For ex:

If today were 01.02.2022, than after 36 months end date is 28.02.2025

05.02.2022		12	04.03.2023		20	04.11.2023		31	04.10.2024		41	04.08.2025
		13	04.04.2023		21	04.12.2023		32	04.11.2024		42	04.09.2025
		14	04.05.2023		22	04.01.2024		33	04.12.2024		43	04.10.2025
		15	04.06.2023		23	04.02.2024		34	04.01.2025		44	04.11.2025
		16	04.07.2023		24	04.03.2024		35	04.02.2025		45	04.12.2025
		17	04.08.2023		25	04.04.2024		36	04.03.2025		46	04.01.2026
		18	04.09.2023		26	04.05.2024		37	04.04.2025		47	04.02.2026
		19	04.10.2023		27	04.06.2024		38	04.05.2025		48	04.03.2026
					28	04.07.2024		39	04.06.2025		49	04.04.2026
					29	04.08.2024		40	04.07.2025		50	04.05.2026
					30	04.09.2024
		51	04.06.2026		61	04.04.2027		71	04.02.2028		81	04.12.2028
		52	04.07.2026		62	04.05.2027		72	04.03.2028		82	04.01.2029
		53	04.08.2026		63	04.06.2027		73	04.04.2028		83	04.02.2029
		54	04.09.2026		64	04.07.2027		74	04.05.2028		84	04.03.2029
		55	04.10.2026		65	04.08.2027		75	04.06.2028
		56	04.11.2026		66	04.09.2027		76	04.07.2028
		57	04.12.2026		67	04.10.2027		77	04.08.2028
		58	04.01.2027		68	04.11.2027		78	04.09.2028
		59	04.02.2027		69	04.12.2027		79	04.10.2028
		60	04.03.2027		70	04.01.2028		80	04.11.2028

 

[RoryA]

So the 29th, 30th and 31st of Jan 2022 should all return 28th Feb 2025 when adding 37 months?

 

[suciulaurentiu]

So the 29th, 30th and 31st of Jan 2022 should all return 28th Feb 2025 when adding 37 months?

29.01.2022, after 37 months, should end in : 28.02.2024, 25, 26, 27, etc....

30.01.2022 ends in bold column:

29.01.2024	29.02.2024
29.01.2025	01.03.2025
29.01.2026	01.03.2026
29.01.2027	01.03.2027
29.01.2028	29.02.2028
29.01.2029	01.03.2029

And 31.01.2022 ends, which is wrong:

30.01.2024	01.03.2024
30.01.2025	02.03.2025
30.01.2026	02.03.2026
30.01.2027	02.03.2027
30.01.2028	01.03.2028
30.01.2029	02.03.2029

 

[suciulaurentiu]

From all my test, I`ve noticed that the only error in calculations are specific in today 31.01.2022... which is driving me crazy

 

[RoryA]

What are the dates in the first columns?

 

[suciulaurentiu]

What are the dates in the first columns?

Dates in the first column are the dates without that + month additional , like 36 not 37 months, we don't need to worry about those, those are correct

 

[RoryA]

I don't understand your logic then. If the dates for 30/1 are correct, why are the dates for 31/1 incorrect, when they all appear to be one day later than the correct ones for 30/1?

 

[suciulaurentiu]

Hi,
So for a better understanding:

Today		Incorrect	Correct ( -1 )
31.01.2022	24months	01.03.2024	29.02.2024
	36months	02.03.2025	01.03.2025
	48months	02.03.2026	01.03.2026
	60months	02.03.2027	01.03.2027
	72months	01.03.2028	29.02.2028
	84months	02.03.2029	01.03.2029

 

[RoryA]

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.