In this article, I will show you some simple Excel formulas you can use to get the end-of-year date in Excel.
Now if you’re wondering why you may even want that, and of course the end of the year date is going to always be 31st December of that year, here is the thing:
Dates work differently in Excel!
Every date in Excel is actually a serial number in the back-end. So when you want to get the date of the last day in a year, you actually need a serial number that represents that date.
In this article, I am going to give you formulas that will return that serial number that you can format to show that as a date.
Using DATE Function
DATE function allows you to construct your own dates and then use them in calculations.
Here is how to use it to get the last day of the current year or any specified year.
Getting Last Day of the Current Year
To get the last day of the current year, you can use the below formula.
=DATE(YEAR(TODAY()),12,31)
This formula uses the TODAY function to get the current date, which is then used within the YEAR function to fetch the year of the current date.
This is then used within the DATE function which needs the year, month, and day value to construct the date:
- Year – This comes from YEAR(TODAY()) which will always give you the year value from the current date.
- Month – hardcoded as 12 as this will never change.
- Day – hardcoded as 31 as this will never change.
Note: Excel fetches the current date using the TODAY function, which relies on your system’s date and time settings. If your system’s settings are incorrect, then the result of the TODAY function will also be incorrect.
You may see the actual serial number instead of the date as the result. All you need to do is format the cell so that is shows the serial number as the date. You can do that by going to the Home tab and and selecting Long or Short date format from the formatting drop-down
Getting Last Day of the Any Specified Year
If you want the last day of a specific year, say (2026 or 2027), you can use the below formula:
=DATE(2026,12,31)
In the above formula, I have hardcoded the year value, but if you have it in a cell reference, you can also use the cell reference. For example, if you have the year value in cell B1, then you can use the below formula.
=DATE(B1,12,31)
Using the DAY 0 Trick in Formulas
This is a clever little trick that uses a quirk in how the DATE function works.
If you use 0 for the day argument, Excel understands this as a request to roll back to the last day of the previous month.
You can use this to find the end of the year in two creative ways.
Formula 1 – Reverting to Previous Year’s Last Day
=DATE(YEAR(TODAY())+1,1,0)
This formula is a bit of a mind-bender, but it’s simple when you break it down:
- YEAR(TODAY())+1 gets the integer for next year (e.g., if it’s 2026, this returns 2027).
- 1 is for the month (January).
- 0 is for the day.
The formula asks Excel for “Day 0 of January of next year.”
Excel calculates this by finding January 1st of next year and then rolling back one day, which lands you on December 31st of the current year.
Formula 2 – Reverting to Previous Months’s Last Day
=DATE(YEAR(TODAY()),13,0)
This is another variation of the same trick. The DATE function is smart enough to handle a month value greater than 12.
When you give it 13 for the month, it doesn’t error. It just rolls forward to the 1st month (January) of the next year.
So, just like the formula above, this is also asking for “Day 0 of Month 13” (which is next year’s January).
The result is the same: it rolls back to the last day of the previous month, which is December 31st of the current year.
Using the EOMOTH function
Excel has an EOMONTH function (which stands for End of Month), that returns a serial number of the last day of the month based on a start date and number of months you want to move forward or backward.
But there is no EOYEAR function in Excel.
However, we can tweak the EOMONTH function to give us the last date of the specified year.
Below is the formula that would give you the last day of the year for the current date.
=EOMONTH(TODAY(), 12 - MONTH(TODAY()))
Here is how this works:
- TODAY() – this gives the current date (which is automatically picked up from your computer date and time settings).
- 12 – MONTH(TODAY()) – this first calculates the month value from the current date and then subtracts it from 12, which gives us how many months are left till the end of the year. For example, if the current date is 15th of February, then this would give us 10
- =EOMONTH(TODAY(), 12 – MONTH(TODAY())) – the EOM month function starts from the current date and then adds additional months that would take us to December, and then gives us the last date in December for the current year.
If you have a date in a cell that you want to use (instead of using the TODAY function), you can use the below formula (considering that the date is in cell B1).
=EOMONTH(TODAY(), 12 - MONTH(TODAY()))
In this article I covered how to get the last day of the current year or any specified year using simple formulas.
I hope you found this article helpful.
Other articles you may also like:
- Calculate Fiscal Year from Date in Excel (Formulas)
- How to Add Week to Date in Excel?
- Combine Date and Time in Excel (Easy Formula)
- Get Day Name from Date in Excel (Easy Formulas)
- Convert Month Name to Number in Excel
- How To Convert Date To Serial Number In Excel?
- How to Get the Number of Days in a Month in Excel?
