Date on expiry of 6 calendar years

[Bazzza67]

Hi board members, I am sure someone can assist me please.

I have a date i.e 08/01/2013 (dd/mm/yyyy format). In an adjacent cell I need to work out a date that is 6 years
following the expiry of that current calendar year.

So 08/01/2013, will show 31/12/2013 + 6 years = 31/12/2019

Many thanks for your help

 

[Fluff]

How about
=EOMONTH(F2,12-MONTH(F2)+72)

 

[jim may]

Had to use 84 (7*12) in order to get your reported answer - that's 7 years...

Anyway - hope this helps..

Excel 2010

	A	B

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]1/8/2013[/TD]
[TD="align: right"]12/31/2019[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=EOMONTH(A1,84-1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Hi,

Modification to jim may's formula so that it'll work for all months of the year:

Book1
	A	B	C
1	1/8/2013		12/31/2019
2	2/15/2014		12/31/2020
3	7/30/2015		12/31/2021
4	12/30/2016		12/31/2022
Sheet367

Cell Formulas
Range	Formula
C1	=EOMONTH(A1,84-MONTH(A1))

 

[Tetra201]

Maybe just

=DATE(YEAR(A1)+6,12,31)