Get End of Year Date in Excel (Formula)

It’s a common task for many Excel users to get the last date of the current year or any specified year.

In this tutorial, I will show you various formulas you can use to return the end-of-year date for any given year in Excel.

Table of Contents

Method #1: Use a DATE + YEAR + TODAY Formula

You can combine Excel’s DATE, YEAR, and TODAY functions in a formula to return the last day of the current year or any other year you specify.

Get End of Year Date of the Current Year

To get the last day of the current year (December 31st of the current year) in Excel, you can use the following formula:

=DATE(YEAR(TODAY()), 12, 31)

The formula uses the TODAY() function to retrieve the current date, extracts the year using the YEAR() function, and then builds the date for December 31st of the current year using the DATE() function.

The formula dynamically extracts the year from the current date derived by the TODAY function, ensuring the result updates automatically each year.

The formula relies on your system’s clock and, therefore, if it is set incorrectly, the result will also be incorrect.

If Formula Returns a Serial Number

Excel stores dates in cells as serial numbers. So, in case the formula returns a serial number, you can format it as a date by opening the Home tab and selecting either Short Date or Long Date on the Number Format drop-down.

Format the cell to show serial number as date.

Get End-of-Year Date of a Specified Year

You can use the formula below to return the last day of any specified year—past, current, or future. In this example, I use 2030, which is currently in the future.

=DATE(2030, 12, 31)

Date formula in Excel to get the last date of the year.

If you do not want to hardcode the year in the formula, you can enter it into a cell, say B1, and then the formula will be:

=DATE(B1, 12, 31)

Method #2: Use Day 0 Tricks with the DATE + YEAR + TODAY Formula

You can specify day 0 in the DATE + YEAR + TODAY formula, which Excel always interprets as the last day of the previous month.

You can apply this trick to get the last day of the current year (or any other year you specify) in two ways, as explained below.

Get Day 0 of Next Year’s January

You can use the formula below to get day 0 of next year’s January, equivalent to the last day of the current year:

=DATE(YEAR(TODAY())+ 1, 1, 0)

The formula works by moving to January 1 of next year, then stepping back one day, to give you December 31 of the current year:

YEAR(TODAY())+ 1 – This part of the formula adds 1 to the current year. For example, if the current year is 2025, the result becomes 2026 (2025 + 1).
=DATE(YEAR(TODAY())+ 1, 1, 0) – If the current year is 2025, the formula becomes =DATE(2026, 1, 0), instructing Excel to return the 0th day of January 2026.
The 0th day of January 2026 = 31 December 2025.

This trick will work for any year you specify because using day 0 always rolls back to the last day of the previous month.

Get Day 0 of Month Value 13

You can use the formula below to get day 0 of month value 13, equivalent to the last day of the current year:

=DATE(YEAR(TODAY()),13,0)

This version of the trick uses a month value of 13, which Excel automatically rolls over to January of the next year, and then applies the day 0 rule to return December 31st of the current year.

Method #3: Use EOMONTH (End of Month) Function

The EOMONTH function in Excel returns the last day of the month, either in the current month or a month before/after a given date.

You can use the function to return a date representing the last day of a specific year after shifting the start date by a given number of months.

The formula below would give you the last day of the year for the current date:

=EOMONTH(TODAY(),12−MONTH(TODAY()))

Notice that the formula returns the last day of the current year as a serial number.

You can format the serial number as a date by opening the Home tab and selecting either Short Date or Long Date on the Number Format drop-down, as shown in Method #1 of this tutorial.

The formula calculates the number of months remaining until the end of the year and adds that number of months to the current date, returning the last day of December:

TODAY() – Returns today’s date as per the system clock. For example, if today is 20 November 2025, TODAY() = 11/20/2025.
MONTH(TODAY()) – Extracts the month number from today’s date. Using the example above, MONTH(11/20/2025) = 11.
12 – MONTH(TODAY()) – Calculates how many months remain until December. Using the example above, 12-11 = 1, meaning December is one month ahead of November.
EOMONTH(TODAY(), 12 – MONTH(TODAY())) – Starts at today’s date, moves forward by the number of months remaining until December, and returns the last day of that month, which is December 31.

If you do not want to use the TODAY function to extract today’s date, you can store the date in a cell, say B1, and reference it directly:

=EOMONTH(B1, 12 - MONTH(B1))

Method #4: Use a Custom LAMBDA Function

If you frequently calculate the last day of the year for different dates, creating a custom LAMBDA function can save time and simplify your formulas.

Instead of repeatedly typing =EOMONTH(date, 12 – MONTH(date)), you can wrap this logic inside a reusable function—just like a built-in Excel function.

Here’s how you can do it:

Open the Formulas tab and click Define Name on the Defined Names group.

The above step opens the New Name feature.

Do the following on the New Name feature:

In the Name box, enter a meaningful name for the function, such as ENDOFYEAR.
In the Refers to box, enter the formula =LAMBDA(d, EOMONTH(d, 12 – MONTH(d))).
Click OK.

Create the LAMBDA formula to end of year

Once you have defined the formula, you can use it exactly like any regular Excel function.

You can use the formula below to return the last day of the year for the date in cell A1:

=ENDOFYEAR(A1)

using ENDOFYEAR formula in the worksheet

Notice that the formula returns the last day of the year for the date in cell A1 as a serial number.

You can convert the serial number into a readable date by going to the Home tab and choosing Short Date or Long Date from the Number Format drop-down, as shown in Method #1 of this tutorial.

Method #5: Use Power Query

In Power Query, you can calculate the end-of-year date for given dates using the M language.

Suppose you have the dataset below.

You can calculate the end-of-year date for each date in the OrderDate column using the steps below:

Select any cell in the dataset.
Open the Data tab and click the From Table/Range option on the Get & Transform Data group.

The above step loads the table data into the Power Query Editor.

Open the Add Column tab and click the Custom Column option on the General tab.

The above step opens the Custom Column feature.

Do the following on the Custom Column feature:

Enter the name of the custom column in the ‘New column name’ box.
Place the cursor after the equal sign in the ‘Custom column formula’ box and enter the formula

= Date.EndOfYear(Date.From([OrderDate]))

Click OK.

Enter the formula in Custom Column dialog box

The above step returns the last date of the year for each date in the OrderDate column.

Click the Close & Load button on the Close group.

The above step loads the query results to a new worksheet in Excel.

When you load the query results into Excel, Excel converts the dates in the EndYearDate column to serial numbers.

You can convert the serial numbers back to readable dates by going to the Home tab and choosing Short Date or Long Date from the Number Format drop-down, as shown in Method #1 of this tutorial.

Method #6: Use a VBA User Defined Function (UDF)

You can use a UDF to get the end-of-year date. Here’s how you can do it:

Copy the code below to a standard module.

Function ENDOFYEAR(d As Date) As Date
ENDOFYEAR = DateSerial(Year(d), 12, 31)
End Function

Use the UDF in a worksheet as a regular Excel function.

Using UDF in worksheet to get last date of the year

The UDF returns dates as serial numbers. You can format the numbers as dates as explained in Method #1.

I hope you found the tutorial helpful.

Other Excel articles you may also like: