I want to get Day name from a specific date on Google Spreadsheet (also tried on Microsoft Excel Online).

I have written specific date on a cell B1: 2020-03-31

then custom number format it as: yyyy-mm-dd

I also have a formula on it's side in A1 =day(B1) the result is Tuesday. This is correct.

But when I enter a new date: 2020-04-01 the result of the formula have gone wrong.

screenshot of the issue

What's causing this and how to solve it? Did I use the wrong formula?


edit: found the issue it was because column of A/C was formatted as number of day of month, not the date itself.

correct formula would be: =TEXT(B1,"dddd")


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

  • You should remove the quotes from "D1". – user82329 Apr 7 '20 at 4:26

did you ever get an answer to this - I am having the same problem. Not only does it return the wrong day of the week - it is not consistent. In other words sometimes it is off by one day (giving me Wednesday instead of Tuesday) and other times it is off by two days so I cannot correct it simply!

Your Answer

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