End of Year

[Guest]

Is there a function that I can use to turn dates into the end of year or the
beginning of the preceed year and for a period in the future.

We have specific "bonus & review" periods which are end of your first
calendar year; beginning of 2nd calendar year; and end of 4th calendar year.

Ie if I have the date 5/30/2001 I would like it to be able to return:

12/31/2001 (end of year)
1/1/2002 (beginning of 2nd calendar year)
12/31/2004 (end of 4th calendar year)

Thanks much!!

 

[Guest]

if your date is in cell A1,

=DATE(YEAR(A1),12,31) gives you end of first year
=DATE(YEAR(A1)+1,1,1) gives you first day in second year
=DATE(YEAR(A1)+3,12,31) gives you last day in 4th year
as per your definitions below.

You will need to format the cells as dates for it to look right.

 

[Guest]

-Right click in the cell where the date is located and choose format cells
-On the number tab select 'Custom' as the category
-Where it says type put the following
mm/dd/yyyy "(end of year)"

Then you can just change what is in the quotes.