calculating end date based on one formula and several attributes

[suciulaurentiu]

Hello, I really really need some help for this situation. I can not seem to find the way out here.

I have these start dates and and end dates . how can i use a formula like this, throughout all year long ?
Formula for now is : =EDATE((DATE(YEAR($A$37);MONTH($A$37)+(I42);DAY($A$37)));1)-1 , where $A$37 is 29.01.2023 and I42 is a number of months like 36.
If I use these, than the calcul is incorrent in several situations like 37, 38 months.

My problem is that these formulas are calculating by the number of months and days in the month, but not days days disregarding the calendar...

37	31.03.2026
38	28.04.2026

I need to have these results:

Start date 29.Jan	End date 28.Feb
Start date 30.Jan	End date 27.Feb
Start date 31.Jan	End date 27.Feb
Start date 01.Feb	End date 28.Feb
Start date 02.Feb	End date 01.Mar
Start date 03.Feb	End date 02.Mar

29.01.2023		12	28.02.2024		20	28.10.2024		31	28.09.2025		41	28.07.2026
		13	28.03.2024		21	28.11.2024		32	28.10.2025		42	28.08.2026
		14	28.04.2024		22	28.12.2024		33	28.11.2025		43	28.09.2026
		15	28.05.2024		23	28.01.2025		34	28.12.2025		44	28.10.2026
		16	28.06.2024		24	27.02.2025		35	28.01.2026		45	28.11.2026
		17	28.07.2024		25	31.03.2025		36	27.02.2026		46	28.12.2026
		18	28.08.2024		26	28.04.2025		37	31.03.2026		47	28.01.2027
		19	28.09.2024		27	28.05.2025		38	28.04.2026		48	27.02.2027
			AN BISECT		28	28.06.2025		39	28.05.2026		49	31.03.2027
					29	28.07.2025		40	28.06.2026		50	28.04.2027
					30	28.08.2025
		51	28.05.2027		61	28.03.2028
		52	28.06.2027		62	28.04.2028
		53	28.07.2027		63	28.05.2028
		54	28.08.2027		64	28.06.2028
		55	28.09.2027		65	28.07.2028
		56	28.10.2027		66	28.08.2028
		57	28.11.2027		67	28.09.2028
		58	28.12.2027		68	28.10.2028
		59	28.01.2028		69	28.11.2028
		60	28.02.2028		70	28.12.2028
						AN BISECT

 

[awoohaw]

What are the dates you want to see? Do you want to see the same day in each month for 40 months?

 

[suciulaurentiu]

Hi!
From what i`ve noticed i`m looking to see only when there is 30th and 31st of each end of the month , results should be end of the monday minus one day, every time. No matter what inverval i put it from 12 up to 84 months.

 

[awoohaw]

Except for February you want last day minus 1 day, for february you want last day?
What version of Excel do you use?

 

[awoohaw]

How about?

Mr Excel Questions.xlsx
	A	B	C
1	Date	New Cacl	New Cacl
2	1/29/2023	0	1/30/2023
3		1	2/28/2023
4		2	3/30/2023
5		3	4/29/2023
Sheet4

Cell Formulas
Range	Formula
C2:C5	C2	=EOMONTH($A$2,B2) - IF(DAY(EOMONTH($A$2,B2))<30,0,1)

 

[suciulaurentiu]

Except for February you want last day minus 1 day, for february you want last day?
What version of Excel do you use?

hello, just the latest office excel 365

 

[awoohaw]

does post #5 work for you?

 

[Fluff]

just the latest office excel 365

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)