Excel: how to find the day of the week from a date

Excel: how to find the day of the week from a date

Do you have a date in your spreadsheet that you want to turn into the day of the week? If so, Microsoft Excel has more features and functions to help you do that. We will show you how to use them.

Ways to find the day of the week from a date in Excel

To find the day of the week from a date, you basically have three ways. The first method is to transform the date into the day of the week in numerical form. In this method, Sunday is displayed as number 1, Monday as number 2, and so on. This is calculated with Excel’s WEEKDAY function.

The second method displays the actual name of the day of the week, such as Sunday, Monday, and so on. This method uses the TEXTExcel function that converts the date to the format of the day.

The third method reformats the actual dates and displays them as the names of the day of the week, such as Sunday, Monday, and so on. If you use this method, you don’t need a separate column to display the days, as the existing date is overwritten.

Find the day of the week as a number

To find the day of the week as a number, use the WEEKDAYExcel function as follows. Counting starts on Sunday, where it is numbered 1. However, you can make the function count from any chosen day (such as Monday), as we will explain below.

To get started, first open your spreadsheet with Microsoft Excel. In the spreadsheet, make sure you have at least one date. Then select the cell where you want to display the day of the week.

Select a cell in an Excel spreadsheet.

In the selected cell, type the following WEEKDAYfunction and press Enter. In this function, replace B2with the cell where you have your date.

=GIORNO DELLA SETTIMANA(B2)

Enter the WEEKDAY function.

If you want to use the date directly in the function, type the following WEEKDAYfunction with your date and hit Enter. Keep the double quotes around your date.

=GIORNOSETTIMANA("24/5/2021")

Enter the WEEKDAY function with direct values.

And in the selected cell, you will see the day of the week for your date as a number.

The result of the WEEKDAY function.

To make the function count the days so that Monday is number 1, change the function as follows. The number 2in the function argument tells him to start counting from Monday.

=GIORNOSETTIMANA(B2,2)

Type the custom WEEKDAY function.

Here’s what your result looks like now:

The result of the custom WEEKDAY function.

To copy the function to other cells, drag down from the lower right corner of the cell where you typed the function.

Copy the WEEKDAY function.

Find the day of the week as a name

To display the day of the week of a date as the name of the day, such as Monday, use the TEXTExcel function. In this function, you tell Excel to format your date in the format of the day. You can choose the abbreviated day name (like Mon) or the full day name (like Monday).

Start by opening your spreadsheet with Microsoft Excel. Then select the cell where you want to display the day of the week.

Click a cell in an Excel spreadsheet.

In the selected cell, type the following TEXTfunction and press Enter. In this function, replace B2with the cell where your date is located.

=TESTO(B2,"gggg")

Enter the TEXT function.

The selected cell will display the day of the week. If you prefer a shortened day name, such as “Mon” for Monday, remove one dfrom the function argument as follows:

=TESTO(B2,"gg")

Type your custom TEXT function.

And now you have the abbreviated day name in the selected cell.

The result of the custom TEXT function.

Convert dates to days of the week

To convert dates to days of weeks without using a separate column, use Excel’s custom number format. Displays the day of the week with its full or abbreviated name.

To do this, first open your spreadsheet with Microsoft Excel. Then select the cells containing the dates.

Select the date cells.

In the Excel ribbon at the top, click the “Home” tab.

Click the "Home" tab in Excel.

On the “Home” tab, from the “Number” section, select “Number Format” (an arrow icon).

Select "Number Format" in the "Home" tab.

A “Format Cells” window will open. In this window, from the “Category” list on the left, select “Custom”.

Select "Custom" in the "Format Cells" window.

On the right, click the “Type” box and enter “dddd” (without quotes) for full day names (like Monday) or “ddd” for shortened day names (like Monday). Then, at the bottom, click “OK”.

Specify the date format.

Excel will transform the selected dates into days of the week.

The dates have become days of the week.

And this is how you know what day it was on a specific date in Microsoft Excel. Very helpful!

Similar Posts

Leave a Reply

Your email address will not be published.