Return first day of a year

This tutorial shows how to get the first day of a year through the use of Excel formulas, with the DATE and YEAR functions

EXCEL FORMULA 1. Return first day of a year using a DATE function with a year

EXCEL

Hard coded formula
Return first day of a year
Cell reference formula
Return first day of a year
=DATE(2019,1,1)
=DATE(B5,1,1)
GENERIC FORMULA

=DATE(year,1,1)

ARGUMENTS
year: The year for which you want to return the first day.

GENERIC FORMULA

=DATE(year,1,1)

ARGUMENTS
year: The year for which you want to return the first day.

EXPLANATION

This formula uses DATE function with the actual year number inserted as the year, with a value of 1 for both the month and day parameters, which will return the first 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 first day of year 2019.

EXCEL FORMULA 2. Return first day of a year using a DATE and YEAR functions with a date

EXCEL

Return first day of a year

=DATE(YEAR(B5),1,1)
GENERIC FORMULA

=DATE(YEAR(date),1,1)

ARGUMENTS
date: A date which contains the year for which you want to return the first day.

EXPLANATION

This formula uses the YEAR function with a date, that contains the year for which you want to return the first day, to return the relevant year. This is then inserted as the year parameter in the date function with the month and day being assigned a value of 1 to represent the first month and day of the nominated year.

In this example the formula returns the first day of year 2019.

RELATED TOPICS

Related Topic Description Related Topic and Description
How to calculate the first day of a current year
How to calculate the last day of a current year

RELATED FUNCTIONS

Related Functions Description Related Functions and Description
The Excel DATE function returns a date through the use of individual year, month and day parameters
The Excel YEAR function returns the year from a specified date