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 the date to the 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.

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

Convert Date to Day of Year in Excel: 4 Methods

Method 1: Convert Date to N^th Day of Year Using Excel DATE and YEAR Functions

The DATE function takes 3 arguments: year, month, and day. And returns the date with supplied values. On the other hand, the YEAR function returns the year of a date in Excel.

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.

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

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 the resultant date (i.e., 2020-12-31) from the given date (i.e., 2021-06-02) in E5.

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

🔁 Today’s Date’s N^th Day of Year

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

The current day is depicted in the following picture.

➤ Type the below formula in cell C5.

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. The whole formula results in a day difference between the last date of the previous year and today.

➤ Hit ENTER to execute the formula and display the N^th day of this year.

Read More: How to Convert Date to Year in Excel

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

In the previous method, we converted the date to the N^th day of the year. However, we can convert dates into day names using the TEXT function. The syntax of the TEXT function is:

The arguments refers to:

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)

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

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.

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’s name.

Read More: Convert Date to Day of Week in Excel

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.

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.

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

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

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

Different day formats offer different types of prioritized presentations 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.

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.

Read More: How to Convert Date to Month and Year in Excel

Download Excel Workbook

Conclusion

In this article, we described features and functions to convert date to day of the year in Excel. The DATE and YEAR functions convert dates to the N^th 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 needs and Excel in their purpose. Comment if you have further inquiries or have anything to add.

Related Articles

• How to Convert Date to Number in Excel
• How to Convert Date to Quarter and Year in Excel
• How to Convert Date to Day in Excel
• How to Convert Date to Month in Excel

<< Go Back to Change Date Format | Date Format | Number Format | Learn Excel