I want to get the day of the week from a specific date in Google Sheets.

I have a date in cell B1 with a custom number format "yyyy-mm-dd": 2020-03-31

In A1 I have the formula =day(B1) which returns Tuesday, which is correct.

However, when I apply the same formula to the date 2020-04-01 the result of the formula is Sunday which is wrong. The correct day of the week is Wednesday

screenshot of the issue

What is causing this and how can I address it?

2 Answers 2


Try This

=TEXT("2020-04-01","dddd") <----------- will result in Wednesday

=TEXT("2020-04-01","ddd") <----------- will result in Wed

or with a specific field determined

=TEXT(D1,"dddd") <----------- will result in Wednesday

=TEXT(D1,"ddd") <----------- will result in Wed

dddd = Long Day Name

ddd = Short Day Name

  1. Your formula =DAY(d) extracts the day number from the date d.
    |    date    | day number |
    | 2020-03-31 |     31     |
    | 2020-04-01 |     01     |    
  2. When you then format the day number as dddd, Sheets coerces it to a valid date:
    |    date    | day number |  "dddd" Formatted    |
    | 2020-03-31 |     31     | 1900-01-30 (Tuesday) |
    | 2020-04-01 |     01     | 1899-12-31 (Sunday)  |
  3. If you want to see only the day of the week from a date then apply the "dddd" formatting to that number don't apply it to the day number. Without knowing the month and year it's impossible to know what weekday a date falls on.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.