↓Skip to main content

Display the Weekday as Text in Excel

·520 words·3 mins
Author
Tiposaurus

I often want to know what day of the week a date is. The obvious choice in Excel is the WEEKDAY() function for this; however it only returns an integer. For example, the formula: =WEEKDAY("01/02/2009") returns 1 (which represents Sunday). In this article, I’ll describe the various ways which you can display the weekday as text in Excel.

Table of Contents 

<span class="ez-toc-title-toggle"><a href="#" class="ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle" aria-label="Toggle Table of Content"><span class="ez-toc-js-icon-con"><span class=""><span class="eztoc-hide" style="display:none;">Toggle</span><span class="ez-toc-icon-toggle-span"><svg style="fill: #999;color:#999" xmlns="http://www.w3.org/2000/svg" class="list-377408" width="20px" height="20px" viewBox="0 0 24 24" fill="none"><path d="M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z" fill="currentColor"></path></svg><svg style="fill: #999;color:#999" class="arrow-unsorted-368013" xmlns="http://www.w3.org/2000/svg" width="10px" height="10px" viewBox="0 0 24 24" version="1.2" baseProfile="tiny"><path d="M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z"/></svg></span></span></span></a></span>

The TEXT() function
#

The easiest way to do this is via the TEXT() function, which you pass the date you want to show the weekday of, and a text code representing how you want it displayed. If the date is in cell A1, some possibilities are:

  • =TEXT(A1, "ddd") – which will show the date as Sat, Sun, Mon, etc
  • =TEXT(A1, "dddd") – will show the date as Saturday, Sunday, Monday, and so on.
  • Other options are available, including "mmm" for month names (and the TEXT() function is handy for formatting things other than dates) – see the TEXT() function help page for more information.

Display Weekday as Text in Excel using VBA Script
#

If the TEXT() function isn’t flexible enough, you can use write a VBA User Defined Function to display it in the way you want. We’ve included an example below.

The function (note that this is not the most elegant implementation possible, but the code is highly readable) is:

' function to return a text representation of the weekday of a given date
' returns the full text, which can be shortened by left(), etc
Function dayText(d As Date) As String
  If Weekday(d) = 1 Then dayText = "Sunday"
  If Weekday(d) = 2 Then dayText = "Monday"
  If Weekday(d) = 3 Then dayText = "Tuesday"
  If Weekday(d) = 4 Then dayText = "Wednesday"
  If Weekday(d) = 5 Then dayText = "Thursday"
  If Weekday(d) = 6 Then dayText = "Friday"
  If Weekday(d) = 7 Then dayText = "Saturday"
End Function

Enter this via the VBA Editor – press Alt + F11, then Insert -> Module and paste this code in. You may wish to add in validation, such as ensuring it can properly handle bad data, such as blank cells or inputs which are not dates.

It can then be called by:

=DAYTEXT("01/02/2009")

which returns Sunday.

This can then be shortened to one or three letters via the LEFT() function, for example:

=LEFT(DAYTEXT("01/02/2009"),3)

returns Sun.

Nested IF()
#

If you want to avoid macros, an alternative method to displaying the weekday as text in Excel is to use the (slightly messy) formula consisting of nested IF(), for a spreadsheet with a date in cell A1:

=IF(WEEKDAY(A1)=1, "Sunday", IF(WEEKDAY(A1)=2,"Monday",IF(WEEKDAY(A1)=3, "Tuesday", IF(WEEKDAY(A1)=4, "Wednesday", IF(WEEKDAY(A1)=5, "Thursday", IF(WEEKDAY(A1)=6, "Friday", "Saturday"))))))

This returns the same as the VBA script above.