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..

 

[RoryA]

Couldn't you just use:

=DATE(YEAR(Start_Date);MONTH(Start_Date)+months_interval;Day(Start_Date)-1)

 

[suciulaurentiu]

Thank you @RoryA , i will try, this will keep also the difference between leap years and intervals ?

 

[RoryA]

It's actually probably safer to use:

=DATE(YEAR(Start_Date);MONTH(Start_Date)+months_interval;Day(Start_Date))-1

I suggest you test it with a variety of dates to make sure it returns the results you want.

 

[suciulaurentiu]

Hi,
I still have errors with the formula , if today is 31.01.2022 i get 02.03.2025 instead of 28.02.2025. I don`t know what is wrong, please help! I really appreciate any input on this!
Just to remember in the initial post:

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

If random months is ex. 24 it calculates fine, if it goes over 36, than the year changes and I get a wrong date again...

 

[suciulaurentiu]

31.01.2022		12	02.03.2023		20	30.10.2023		31	30.09.2024		41	30.07.2025		81	30.11.2028
		13	30.03.2023		21	30.11.2023		32	30.10.2024		42	30.08.2025		82	30.12.2028
		14	30.04.2023		22	30.12.2023		33	30.11.2024		43	30.09.2025		83	30.01.2029
		15	30.05.2023		23	30.01.2024		34	30.12.2024		44	30.10.2025		84	02.03.2029
		16	30.06.2023		24	01.03.2024		35	30.01.2025		45	30.11.2025
		17	30.07.2023		25	30.03.2024		36	02.03.2025		46	30.12.2025
		18	30.08.2023		26	30.04.2024		37	30.03.2025		47	30.01.2026
		19	30.09.2023		27	30.05.2024		38	30.04.2025		48	02.03.2026
					28	30.06.2024		39	30.05.2025		49	30.03.2026
					29	30.07.2024		40	30.06.2025		50	30.04.2026
					30	30.08.2024
					51	30.05.2026		61	30.03.2027		71	30.01.2028
					52	30.06.2026		62	30.04.2027		72	01.03.2028
					53	30.07.2026		63	30.05.2027		73	30.03.2028
					54	30.08.2026		64	30.06.2027		74	30.04.2028
					55	30.09.2026		65	30.07.2027		75	30.05.2028
					56	30.10.2026		66	30.08.2027		76	30.06.2028
					57	30.11.2026		67	30.09.2027		77	30.07.2028
					58	30.12.2026		68	30.10.2027		78	30.08.2028
					59	30.01.2027		69	30.11.2027		79	30.09.2028
					60	02.03.2027		70	30.12.2027		80	30.10.2028

 

[RoryA]

The reason is that the formula adds 37 full months in your example, which takes it technically to the 31st of Feb 2025. Since the last day of Feb is the 28th, the 31st is really the 3rd of March. Subtracting 1 from that is then the 2nd March. Why should it be the 1st?

 

[suciulaurentiu]

@RoryA, thank you for the quick input! I should have for today, the end date of : 28.02.2025, and of course all the end dates that goes with it...currently my formula ends me up in 02.03.2025

 

[suciulaurentiu]

no idea

 

[RoryA]

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