Often in workplaces or in other many reasons we need to encounter dates. Dates are quite common in our daily Excel workflow. From tracking down employee salary payments to managing supply chain goods, dates are a must thing to add an Excel worksheet. But at the same time, we need to know the name of the day of a particular date. Tracking these can be a pretty hectic thing to do manually. To answer the question of how we can insert day and Date in Excel is explained below with adequate demonstrations.
Download Practice Workbook
Download this practice workbook below.
3 Ways to Insert Day and Date in Excel
For the demonstration purpose, I am going to use the simple dataset below. In this dataset, only the dates that need the name of the day are included.
1. Use of TEXT Function to Insert Day and Date
The TEXT function is used in Excel to convert text, and numbers to character strings efficiently. In this case, it will take the date as an input and return the name of the day with dates in character string format.
- First, select Cell C5 and enter the following formula:
- After entering the formula, you will notice that Cell C5 now shows the name of the day of the dates mentioned in Cell B5.
- Next, hold the Fill Handle icon at the corner of Cell C5. And drag it down to Cell C14.
- Now all of the dates in the Days column are showing the name of the days in the column.
2. Using Number Formatting in Excel
Using number formatting is a very convenient method of formatting dates to have their name of the day. It can be done in two separate ways, one is by using options from the ribbon. Other is using custom formatting.
2.1 From Ribbon
Formatting the dates by using the ribbon is the most convenient and efficient one.
- To add the name of the day to your worksheet date, at first you need to select dates from your workbook and then go to number formatting in the Number group in the Home tab.
- After clicking the number formatting icon, a drop-down menu containing various types of formatting will appear, from that menu select the Long Date format, which includes the date with the name of the day.
- After selecting the Long Date format, you will notice all your dates now have the corresponding name of the day.
- How to Insert a Date Picker in Excel (With Step-by-Step Procedure)
- Enter Time in Excel (5 Methods)
- How to auto populate date in Excel when cell is updated
2.2 From Context Menu
Formatting dates with the name of the day can also be done by just bringing up the context menu by right-clicking the mouse.
- To add the name of the day of the dates you need, you first must select the dates that need to have a weekday name.
- After selecting the dates, right-click on your mouse to open the context menu. From the context menu, select Format Cells.
- After selecting Format Cells, a new window will open. From that window click on Number. Then, select Date from the Category. In the Type field, select the format with the date alongside the name of the day. Then click OK.
- After clicking OK, you will notice that all the dates are now equipped with the name of the days.
3. Applying Custom Number Formatting in Excel
Custom formatting the dates is another effective way to rewrite dates with the name of the day.
- To include the name of the day by Custom Formatting, select the range of cells B5:B14.
- Selecting the cells, right-click the mouse button, clicking the button will open the context menu, and from that menu, select Format Cells.
- Then a new window will launch, from that window click on Number. Then, select Date from the Category. In the Type field, select the m/d/yyyy format if it is not there already. Then type “dddd” with m/d/yyyy in the type of bar. And click OK.
This “dddd” will add the name of the day in full form. If you want the format to be an abbreviation format, simply write “ddd” instead of “dddd”.
- After clicking OK, you will notice that all the dates now have the name of the days with them.
In this way, you can insert the day and date in Excel easily.
To sum it up, the question “how to insert day and date in Excel” is answered here in 3 different ways. First, we utilized the TEXT function to format dates to have the name of the day. Then we used formatting tools to add the name of the day to dates. Among them was using number formatting with ribbon and context menu. Another one is to use custom formatting to add the name of the days in dates.
For this problem, a workbook is available for download where you can practice these methods.
Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the Exceldemy community will be highly appreciable.