How to calculate Day of the Year in Excel

by

An easy formula that returns the day of the year for a given date. There’s no built-in function in Excel that can do this.

1. To get the year of a date, use the YEAR function.

Worked Example:   How to generate random date between two dates in Excel

2. Use the DATE function below to return January 1, 2016. The DATE function accepts three arguments: year, month and day.

3. The formula below returns the day of the year.

Worked Example:   Highlight rows with dates between In Excel

Explanation: dates and times are stored as numbers in Excel and count the number of days since January 0, 1900. June 23, 2016 is the same as 42544. January 1, 2016 is the same as 42370. Subtracting these numbers and adding 1 gives the day of the year. 42544 – 42370 + 1 = 174 + 1 = 175.

Worked Example:   DATE function: Description, Usage, Syntax, Examples and Explanation

Leave a Reply

Your email address will not be published. Required fields are marked *