# How to Convert Date to Day of Year in Excel: 4 Methods

### Method 1 – Convert Date to Nth 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 Nth day in all cells, as shown in the following picture.

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.

âž¤ 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.

âž¤ Hit ENTER to execute the formula and display the Nth 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Â NthÂ 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.

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.

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF