The Weekdays in Excel

There are a few ways of getting the Weekday in Excel. It depends on the requirement that 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 then WEEKDAY function.

If you require short day name of the Week e.g. Sun, Mon, Tue then use the TEXT function with formatting code “ddd”. For full day name (Sunday, Monday etc.) use the dddd formatting code in TEXT function.

You may also calculate the number of working days between two dates. The best way of learning 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])

  • The serial_number is where the date is given. You should use DATE function to provide the date in WEEKDAY function rather than text. The date returned by other functions or formulas can also be used there.
  • The return_type is an optional parameter that specifies the type return value by WEEKDAY function. The 1 is 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 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 WEEKDAY function

In this example, I used “12/30/2017” date which is Saturday in the WEEKDAY function. For making things clear, I used all return_type codes in B column cells so you understand how it works:

Excel WEEKDAY

The following formulas are used and it 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:

Excel WEEKDAY 14

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

Excel WEEKDAY 11

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 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:

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

Excel WEEKDAY TEXT

Full day name example

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

The formulas:

and

The resultant sheet:

WEEKDAY full name

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 cell” dialog should appear.

WEEKDAY date format

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.

WEEKDAY date format 2

In the Type text box, enter ddd for 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:

Excel NETWORKDAYS

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:

Excel NETWORKDAYS holidays

You can see, 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.