Search Not Just Numbers

Tuesday 7 April 2015

Excel Tip: Showing the weekday for a date

I hope everyone who celebrates it has had a good Easter break. I've just got a quick post to ease you back into work this week.

Have you ever wanted to show the weekday's name in a date? Or, show the name of the weekday for any particular date. Excel makes this much easier than you might think.

You can do this using number formats.

If you right-click a cell with a date in it and select Format Cells, Number Format, then select Custom, you can design your own number format by typing into the Type: box.

A typical UK date format, might be dd/mm/yyyy. This will show the day and month as a two digit number and the year as a four digit number.

So, for example, the 7th April 2015 would be 07/04/2015.

The day, could be shown as ddd or dddd, and these work as follows:

ddd gives the three letter version of the day, e.g. Tue

whereas dddd gives the full name of the day, e.g. Tuesday

The month works similarly, so mmm would return Apr and mmmm would return April.

We could type a custom format such as:

dddd, dd mmmm yyyy

This would show the same date as:

Tuesday, 07 April 2015

Alternatively, you could simply show the day itself by using a format as:

dddd

Using this format, the cell will still hold the full date, but will just display the day, e.g. Tuesday.

You can also use this format in the TEXT function, to return the name of the day as text in another cell.

Say that the date (07/04/2015) is in cell A1, then:

=TEXT(A1,"dddd")

will return the text Tuesday.



If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

5 comments:

  1. also "mmmmm" gives the month as a single letter (J F M A M J J etc)
    sadly no equivalent for days (use LEFT and TEXT if you need this)
    "e" gives the full year (="yyyy") - but this does NOT export successfully to Access!

    Jim

    ReplyDelete
  2. I've been wanting to know how to do this for ages! Thanks.

    ReplyDelete