Maruf Islam
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.
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.
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.
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 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.
➤ Hit ENTER to execute the formula and display the Nth day of this year.
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.
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 name.
Read More: How to Convert Text to Date with Excel VBA (5 Ways)
Similar Readings:
- How to Convert a Date to dd/mm/yyyy hh:mm:ss Format in Excel
- Get First Day of Month from Month Name in Excel (3 Ways)
- How to Get Last Day of Previous Month in Excel (3 Methods)
- Convert 7 Digit Julian Date to Calendar Date in Excel (3 Ways)
- How to Stop Excel from Auto Formatting Dates in CSV (3 Methods)
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.
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.
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 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.
