Excel4Business - Our Blog

Excel Tips and Tricks from our Experts

Excel Formula Help - using WEEKDAY to return days of the week

With the WEEKDAY function, Microsoft Excel is able to automatically provide the day of the week in relation to a date entry. However, as will all dates, Excel returns the day of the week in numeric format, as we can see when we use the formula example below. Here WEEKDAY returns the number 2 for 15th of April 2013, which represents Monday.

Hit Enter,

As we can also see in the example above, we have created a table that maps the numeric day to the actual word. We do this so that we can create a Range Name for the days and their corresponding numbers that we can use in our formula. For more on Range Names, see this earlier blog. We select the table and name our Range Name, _DOW for Day Of Week using the filed in the top left corner next to the formula bar:

We can now edit the formula. First we begin with VLOOKUP and then within brackets include the WEEKDAY formula we already have, followed by our Range Name _DOW. This if followed by a 2 representing the column of the number/weekday table and then FALSE to indicate that we require an exact match:

Finally we copy down to recieve our corresponding days of the week.

For more on using WEEKDAY and other time related formulas, or for all advice and suggestions for using Excel, contact our Experts. The Microsoft Excel Help page for WEEKDAY is also worth viewing and can be found here.