End of Year
 

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

Answer: End of Year
 

Yes, there is a function in Microsoft Excel that you can use to turn dates into the end of the year or the beginning of the preceding year. You can use the EOMONTH function to get the end of the month for a given date and then subtract the number of months to get the end of the year or the beginning of the preceding year.

Here are the steps to get the end of the year for a given date:

1. Select a cell where you want to display the end of the year.
2. Enter the following formula:
   Formula:

   ---

   =EOMONTH(A1,11) 


   ---

3. Replace A1 with the cell reference of the date you want to use.
4. Press Enter.

This formula will return the last day of December for the year of the date in cell A1.

To get the beginning of the second calendar year, you can use the following formula:

1. Select a cell where you want to display the beginning of the second calendar year.
2. Enter the following formula:
   Formula:

   ---

   =DATE(YEAR(A1)+1,1,1) 


   ---

3. Replace A1 with the cell reference of the date you want to use.
4. Press Enter.

This formula will return the first day of January for the year following the year of the date in cell A1.

To get the end of the fourth calendar year, you can use the following formula:

1. Select a cell where you want to display the end of the fourth calendar year.
2. Enter the following formula:
   Formula:

   ---

   =EOMONTH(DATE(YEAR(A1)+3,1,1),11) 


   ---

3. Replace A1 with the cell reference of the date you want to use.
4. Press Enter.

This formula will return the last day of December for the fourth year following the year of the date in cell A1.

End of Year
 

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.

--
Allllen


"Dedrie" wrote:

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

End of Year
 

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


"Dedrie" wrote:

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