# Display the Weekday as Text in Excel

I often want to know what day of the week a date is. Excel has a function for this, but unfortunately it only returns an integer, for example the function:

`=WEEKDAY("01/02/2009")`

returns `1`

(which represents Sunday).

## 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 info.

## 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 (possibly not the most elegant implementation possible) is:

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`

.

## Nexted IF()

If you want to avoid macros, an alternative method 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.