0

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?

0

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.
  • Thank you. I tried changing the format of the date, from 5/5/2019 to 5-5-2019 and got the same error. – jen Feb 6 at 14:07
  • @jen do not change the format to 5-5-2019 change it to 0. Format>Number>More Number Formats>Custom Number Formatting. Then erase whatever is in that dialog box and just type a 0. – MattKing Feb 6 at 22:48
  • Please read how to and share a test sheet so as you can be easier helped. – marikamitsos Feb 7 at 16:59
0

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

  • Thanks. I'll try this. But it still thinks the year is 1899. – jen Feb 6 at 14:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.