Working with Weekdays: WEEKDAYS and TEXT Functions in Excel

Working with Weekdays in Excel

There are a few ways of getting the Weekday in Excel. It depends on the requirement of which way to use.
For example, if you require the number of the day in a Week for the given date (1 for Sunday and 7 for Saturday) then use the WEEKDAY function.
If you require a short day name of the Week e.g. Sun, Mon, Tue then use the TEXT function with formatting code “ddd”.
For full-day names (Sunday, Monday, etc.) use the dddd formatting code in the TEXT function.
You may also calculate the number of working days between the two dates.
The best way to learn how these date functions work is to show by way of demos. So, let us go through these functions one by one with examples.

Excel WEEKDAY function

The general syntax of using the WEEKDAY function is:

WEEKDAY(serial_number,[return_type])

Argument	Description
serial_number	The serial_number is where the date is given. You should use the DATE function to provide the date in the WEEKDAY function rather than text. The date returned by other functions or formulas can also be used there.
return_type	The return_type is an optional parameter that specifies the type return value by the WEEKDAY function. 1 is the default value which means 1 will be returned for Sunday, 2 for Monday, and 7 for Saturday.

Provide the return_type 2 if you require a count for Monday as 1, Tuesday = 2, and Sunday = 7.

*Use the value 3 for Monday as 0 and Sunday = 6.

The example of using the WEEKDAY function

In this example, I used the “12/30/2017” date which is Saturday in the WEEKDAY function.

To make things clear, I used all return_type codes in B column cells so you understand how it works:

The following formulas are used and they returned different values for the same date i.e. Saturday:

=WEEKDAY(DATE(2017,12,30)) returned 7
=WEEKDAY(DATE(2017,12,30),1) returned 7
=WEEKDAY(DATE(2017,12,30),2) returned 6 as day count started from Monday i.e. Monday = 1
=WEEKDAY(DATE(2017,12,30),3) returned 5 as day count started for Sunday = 6, Monday =0 and so on.

Did you know? From Excel 2010, you may also use 11 to 17 values for the return_type argument. The value 11 means Monday =1 and Sunday =7. For the value 12, Tuesday =1 and Monday =7 and so on. If you apply the above formula with 14 value, the result will be:

The WEEKDAY formula with 14 value:

=WEEKDAY(DATE(2017,12,30),14)

The result should be 3 as shown in the graphic below:

Finally, have a look at the returned result for all values; 11 through 17 for the same date i.e. 2017,12,30 (Saturday):

If you want to copy the formula, these are listed below:

=WEEKDAY(DATE(2017,12,30),11)

=WEEKDAY(DATE(2017,12,30),12)

=WEEKDAY(DATE(2017,12,30),13)

=WEEKDAY(DATE(2017,12,30),14)

=WEEKDAY(DATE(2017,12,30),15)

=WEEKDAY(DATE(2017,12,30),16)

=WEEKDAY(DATE(2017,12,30),17)

Get the Weekday as the short name by TEXT function

If you require getting the Weekday in short name format e.g. Mon, Tue, Wed, etc then use the TEXT function with “ddd” formatting code as shown in the demo below:

The TEXT formula:

=TEXT(DATE(2017,12,30),”ddd”)

The result for the same date as used in the above examples:

Full day name example

Similarly, you may use the “dddd” formatting code to get the full day name of the Week. See the formula and result below:

The formulas:

=TEXT(DATE(2017,12,30),”dddd”)

and

=TEXT(A3,”dddd”)

The resultant sheet:

For learning more about the TEXT function, go to its detailed tutorial: MS Excel TEXT function. It contains many examples for formatting dates; not only day names of the Week but months, years, and formatting dates in different patterns.

Display the Weekday by Format Cell option

By using the “Format Cells” option, you may display the dates with Weekday names or many other formats for the single cell, multiple cells, or ranges.

For that, right-click on the cell or select multiple cells and right click –> Format cells. The “Format Cells” dialog should appear.

Select the desired format and press OK. This should reflect on the selected date cells as shown in the above graphic.

If you wish to display just the day name (full or short), go to “Format cells” dialog after selecting the date cells. Select the Custom under the Category as shown below.

In the Type text box, enter ddd for the short name of the day or dddd for the full name and press OK.

You may learn more about this option in detail here: Changing the date format tutorial.

How to get the number of weekdays between two dates?

If you need to get the number of weekdays (working days) between two given dates then use the NETWORKDAYS function.

The NETWORKDAYS function excludes the weekends and optionally any other holidays between two dates.

Let us first have a look at the example of getting the number of weekdays between two days:

The NETWORKDAYS formula:

=NETWORKDAYS(A2,B2)

The resultant sheet:

Now let us have a look at the number of working days as holidays are specified.

The formula for calculating working days with holidays:

=NETWORKDAYS(A2,B2,C2:C4)

The result:

You can see that three more days are reduced as compared to the last example between two given dates. The range of holidays is given as the third argument in the NETWORKDAYS function.