How to Convert Date to Day of Year in Excel (4 Methods)

While working with dates in Excel, we occasionally need to convert dates to day numbers or names. Also, to count day differences we convert date to day of the year in Excel. Multiple features and functions such as DATE, YEAR, TEXT, and TODAY convert date to day of the year in Excel.

Let’s say, we have a dataset where we have employee data such as Employee ID, Name, and Joining Date. We want the day (i.e., Day Number or Name) from respective joining dates.

Dataset-Convert Date to Day of Year in Excel

In this article, we demonstrate multiple features and functions to convert date to day of the year in Excel.


Download Excel Workbook


4 Easy Ways to Convert Date to Day of Year in Excel

Method 1: Convert Date to Nth Day of Year Using Excel DATE and YEAR Functions

The DATE function takes 3 arguments: year, month, and day. And the function returns the date with supplied values.

Step 1: Add an additional column beside the range. Before inserting the formula in the cell, format the cells in General or Number type format using Format Cells or Number Format display box.

 

Date and Year function-Convert Date to Day of Year in Excel

Step 2: Type the following formula in any blank cell (i.e., E5).

=D5-DATE(YEAR(D5),1,0)

DATE(YEAR(D5),1,0) portion of the formula returns the last day of the previous year date 2020-12(Dec)-31(day). The formula subtracts resultant date (i.e., 2020-12-31) from the given date ((i.e., 2021-06-02)) in E5.

Formula insertion

 Step 3: Press ENTER and drag the Fill Handle to display the Nth day in all cells as shown in the following picture.

Fill handle


🔁 Today’s Date’s Nth Day of Year

By slightly altering the previous formula we can convert the current date to the Nth day of this year.

The current day is depicted in the following picture.

today function

Type the below formula in C5 cell.

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

The TODAY function results in today’s date (i.e., 2022-03-24). The DATE(YEAR(TODAY()),1,0) portion of the formula brings the last date( i.e., 2021-12-31) of the previous year. And the whole formula results in a day difference between the last date of the previous year and today.

Formula insertion

Hit ENTER to execute the formula and display the Nth day of this year.

Result

Read More: How to Convert Date to Year in Excel (3 Quick Ways)


Method 2: Using TEXT Function to Convert Date to Day in Excel

In the previous method, we convert the date to the Nth day of the year. However, we can convert dates in day names using the TEXT function. The syntax of the TEXT function is

=TEXT (value, format_text)

The arguments refer

value; a given value to convert.

format_text; the number format in which the value appears.

Step 1: Paste the below formula in any blank cell (i.e., E5)

=TEXT(D5,"DDD")

By comparing the syntax, D5= value and “DDD”= the format_text we want the value in.

text function-Convert Date to Day of Year in Excel

Step 2: Use the ENTER key to display the day names of respective dates. Then, drag the Fill Handle to make all the other day names visible.

text function

Since we insert only the day (with 3 initial letters) to appear, Excel displays the day name from the date. You can use more or less initial letters to display the day name.

Read More: How to Convert Text to Date with Excel VBA (5 Ways)


Similar Readings:


Method 3: Convert Date to Day of Year Using the Excel Format Cells Dialog Box

Alternative to the TEXT function, Excel’s Format Cells feature can display day names from dates.

Step 1: Select all the dates you want to convert. Go to the Home tab > Click on the Font Setting icon as depicted in the following screenshot.

Format cells-Convert Date to Day of Year in Excel

Step 2: The Format Cells window opens up. In the Format Cells window,

Click on the Number section.

Select Custom from the Category section.

Type “ddd” under Type.

Click on OK.

Format cells

In a moment, all the dates convert into day names as shown in the below screenshot.

Final result

For keeping things standard, we display 3 initial letters of day names. You can display the whole day’s names in the cells.

Read More: How to Convert Date to Day of Week in Excel (8 Methods)


Method 4: Displaying Long Date to Convert Date into Day of Year in Excel

Different day formats offer different types of prioritized presentation of dates. Excel’s Long Date date format displays day names with month and year.

Step 1: Highlight all the entries then Go to the Home tab > Click on the Number Format icon (Number section) > Select Long Date.

long date-Convert Date to Day of Year in Excel

Step 2: Clicking on the Long Date converts all the dates into full-day names, months, and years. From there you can easily see the day names along with the year.

long date

Read More: How to Convert Date to Month in Excel (6 Easy Methods)


Conclusion

In this article, we described features and functions to convert date to day of year in Excel. The DATE and YEAR functions convert dates to the Nth day of the year. The TEXT function, Format Cells, and Long Date fetch the particular day name of the date. Hope these above-mentioned methods fulfill your need and excel in their purpose. Comment if you have further inquiries or have anything to add.


Related Articles

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo