Adding 12 months to a year

[Stanman]

Hi,

I am trying to add a year to a date and end it on Sunday ie

Start date is 5th April 2021, I would like to add a year to this and have tried =EDATE(E6,12) and =DATE(YEAR(E6) + 1, MONTH(E6), DAY(E6)) but it returns 04/04/2021, which is a Monday, the calculation is doing what it needs to but other than adding a -1, is there a clever way of ending the date on the Sunday year on?

 

[etaf]

i get 5/4/22
anyway
you could just add days
=DATE(YEAR(E6), MONTH(E6), DAY(E6)+363)
is this a one off or a recurring formula that you all ways want to end on a sunday nearest to the year

 

[Fluff]

If you want the Sunday before the year is up, maybe

=WORKDAY.INTL(E6,52,"1111110")

 

[Stanman]

i get 5/4/22
anyway
you could just add days
=DATE(YEAR(E6), MONTH(E6), DAY(E6)+363)
is this a one off or a recurring formula that you all ways want to end on a sunday nearest to the year

HHI,

I will be reusing it at the start of each financial year, so start date would change and end date.

 

[steve the fish]

You will just start creeping away from the 6th April if you want to use a day. There arent 52 weeks in a year. Its why date systems use week 53 sometimes.

 

[Dave Patton]

Since some years have 53 weeks, consider the following

T202110a.xlsm
	A	B	C
1
2		Mon 05-Apr-21	Sun 03-Apr-22
3		Mon 04-Apr-22	Sun 02-Apr-23
4		Mon 03-Apr-23	Sun 07-Apr-24
2c

Cell Formulas
Range	Formula
C2:C4	C2	=WORKDAY.INTL(B2,52+(MONTH(WORKDAY.INTL(B2,52,"1111110"))=3),"1111110")
B3:B4	B3	=C2+1

 

[Rick Rothstein]

If the Sunday that you want is on or before the date one year hence, then give this formula a try...

=EDATE(B2,12)-WEEKDAY(EDATE(B2,12))+1

 

[Tetra201]

Another guess: if the start day is the first Monday in April, then the formula for the end date is:

=WORKDAY.INTL(EDATE(EOMONTH(B2,-1),12),1,"0111111")-1