Return last day of a year
This tutorial shows how to get the last day of a year through the use of Excel formulas, with the DATE and YEAR functions
Hard coded formula
Cell reference formula
|
=DATE(2019,12,31)
=DATE(B5,12,31)
|
GENERIC FORMULA
=DATE(year,12,31)
ARGUMENTS GENERIC FORMULA
=DATE(year,12,31)
ARGUMENTS EXPLANATION This formula uses DATE function with the actual year number inserted as the year, with a value of 12 for the month parameter that represents the last month of a year and 31 for the day parameter that represents the last day of December. This will return the last day and month of the nominated year.
Click on either the Hard Coded or Cell Reference button to view the formula that has the year directly entered into the formula or referenced to a specific cell.
In this example the formula returns the last day of year 2019. |
|
GENERIC FORMULA
=DATE(YEAR(date),12,31)
ARGUMENTS EXPLANATION This formula uses the YEAR function with a date, that contains the year for which you want to return the last day, to return the relevant year. This is then inserted as the year parameter in the date function with the month value of 12 and day value of 31 to represent the last month and day of the nominated year.
In this example the formula returns the last day of year 2019. |
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Return first day of a year | How to get the first day of a year | |
| Return first day of a current year | How to calculate the first day of a current year | |
| Return last day of a current year | How to calculate the last day of a current year |
RELATED FUNCTIONS
| Related Functions | Description | Related Functions and Description |
|---|---|---|
| DATE Function | The Excel DATE function returns a date through the use of individual year, month and day parameters | |
| YEAR Function | The Excel YEAR function returns the year from a specified date |