Weekday Formula Error

Question

I'm trying to use the Weekday formula. However, instead of getting a 0-7 number returned, I am getting 12/31/1899. I have watched several videos, tried on different browsers and devises. What can I do?

Answer 1

If you are seeing 12/31/1899 instead of 0 this is because the cell format number is set to date instead to number.

To fix this, click Format menu then Number then Number

Answer 2

Jen, if the information below doesn't solve your problem, please provide your existing formula that is failing so we can help you to correct it. Currently we have to guess at the specific problem you might be having.

My own 'guess' is you have a formatting issue. I suggest you make sure that the number format of the cell with the formula is not a date. If you format a number that is not a date (ex: 1-7) as a date, you will get a date returned which is the result you provided (12/31/1899).

Here is a link to a succinct overview of Weekday at support.google: Weekday Explained

I recommend reviewing the notes in that article. In particular:

• Insure that the input to the function is either a reference to a cell containing a date, a function which returns a date object such as DATE, DATEVALUE or TO_DATE, or a date serial number of the type returned by the N function. Google Sheets represents dates internally as numbers for ease of use in calculation, and while this conversion is done automatically when a date in the form of a string is input into a cell, this function does not perform this conversion.
• WEEKDAY does not autoconvert number formats in the same way that Google Sheets does upon direct entry into cells. Therefore, WEEKDAY(10/10/2000) is interpreted as WEEKDAY(0.0005), the quotient of 10 divided by 10 divided by 2000.