Display Weekdays names

This is a common task. We have a list of dates and we want to get the corresponding weekdays. We can use the WEEKDAY() function to get it and the result will be like this:

WEEKDAY1

On column A we have our dates and on column B, using the WEEKDAY() function we get the number corresponding to the weekday. But this is not a good way of displaying the weekday, we would like it to display it as the name of the weekday, not a number, right? There are numerous ways that we can change that. I’m going to show you two of them.

The first, and simplest way, is to format column B with a custom format. For that, just select the values on column B and right-click to select Format Cells and on the Number tab, choose the Custom Category and insert Type as dddd. This will give the names of the weekdays like “Sunday”. If you insert a type ddd it will show you the names like “Sun”.

WEEKDAY2

This is how it will look like after the formatting:

WEEKDAY3

Another way you could do this is using the CHOOSE() function with a formula like this on cell B2 and then copy down:

=CHOOSE(WEEKDAY(A2),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")

The result will be the same. This last method is just to show you that there are lots of ways of doing the same thing in Excel.

4 comentários:

tarun manik said...

Thanks, the first option helped

Anonymous said...

I found the following to be the better option:

=TEXT(WEEKDAY(A1),"dddd")

Just change the return type of WEEKDAY if required.

Anonymous said...

Hi, the second formula is very helpful! Thank you.
Does anyone know how to run a date series that is Monday - Friday, for a year, so that weekend dates are not included?

Kadr Leyn said...

Day name can be gotten from date with Weekday - WeekdayName Functions in Vba Codes .For example:
"Label1.Caption = WeekdayName(Weekday(TextBox1, 0), False, 0)"

I used these functions in I made userform that to insert adding a quick date to active cell as an alternative to the calendar and date picker .

Date form can be reviewed here :https://www.youtube.com/watch?v=QOhg6geNoJE