How to 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

Step 1: An additional column beside the range. 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 prior year and today.

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

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 refer 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. Drag the Fill Handle to make all the other day names visible.

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.

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

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

Step 1: Highlight all the entries; 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.

Download Excel Workbook

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 Date Format | Number Format | Learn Excel