Get the Right Day In Excel

Formulas and formats

To get the day of the week in a cell you have two choices. You can use a format or a formula.

DAY

Format Method

You can use a custom format to display the day of the week of a date. To show the short name use ddd to show the full name use dddd. See the two examples below.

Short Day Custom Format

Long Day Custom Format

Formula method

If you want to use the Day name as text use the TEXT function to convert it to text. The formulas used in the image below are

Cell E2

=TEXT(A2,"ddd")

Cell F2

=TEXT(A2,"dddd")

Cell G2

=TEXT(A2,"ddd dd/mm/yy")

Different Day results

Note: the format cells (columns B and C) are right aligned as dates are treated as numbers. The TEXT function cells (columns E, F and G) are left aligned because the TEXT function returns a text result.

If you wanted to show just the first letter of the day eg M for Monday for a roster or a chart, you could use the following formula

=LEFT(TEXT(A2,"ddd"))

 

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *