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).

=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 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.

Fill handle

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.

today function

➤ Type the below formula in cell C5.

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

Formula insertion

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

Result

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:

=TEXT (value, format_text)

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)

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

text function

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.

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

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.

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.