Weekday is a very useful little function. Quite simply, it works out which day of the week a date represents.

Given that dates are simply formatted numbers, it can be used against any cell that contains a number. But it's probably safest to only apply it to cells formatted as dates.

The formula returns a number to represent the day of the week (Monday–Sunday). The second argument in the formula determines what that number represents.

If the cell being referenced contains any time within a given date, it will be treated as that date and the formula will correctly bring back the correct number.

Note that the formula yields an incorrect result for dates between 1 January,1900 and the non-existent 29 February, 1900 owing to a purposeful Excel bug.



Cell is the cell containing the date in question.

Number can take the value 1, 2 or 3, and controls how the number comes back.

  • 1: This returns a number between 1 (Sunday) and 7 (Saturday)
  • 2: This returns a number between 1 (Monday) and 7 (Sunday). I always choose this option
  • 3: This returns a number between 0 (Monday) and 6 (Sunday).


If cell A1 contains the value 5 July, 2011, then:

=WEEKDAY(A1,1) will bring back 3

=WEEKDAY(A1,2) will bring back 2

=WEEKDAY(A1,3) will bring back 1.

This entry was posted in Formulae explained and tagged , . Bookmark the permalink.

One Response to WEEKDAY

  1. Michelle says:

    It sounds like the Number you choose depends on whether you count starting with 0 or 1 and whether you consider Sunday or Monday to be the first day of the week... interesting function. Thanks for sharing!

Leave a Reply

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