Share via

Excel DAY and TEXT function not returning correct day name

Anonymous
2018-08-01T17:20:38+00:00

Hello,

The following formula is not returning the correct day name for August.  It works correctly for July dates.

=TEXT(DAY(L2),"dddd")

Where L2 is the cell with the date, for example 8/1/2018

For 8/1/2018, the result is incorrectly "Sunday".  With date 7/31/2018, the result is correct as "Tuesday".

Any ideas what is happening?

Thanks for help.Tim

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

DaveM121 891K Reputation points Independent Advisor
2018-08-01T17:26:40+00:00

Hi Tim, simplify your formula to this, and it will work:

=TEXT(L2,"dddd")

Was this answer helpful?

5 people found this answer helpful.
0 comments

Answer accepted by question author

Vijay A. Verma 104.8K Reputation points Volunteer Moderator
2018-08-01T17:35:38+00:00

Great! I am glad that worked! If you don't have any more questions, feel free to choose a rating and have a great day!

Was this answer helpful?

1 person found this answer helpful.
0 comments

Answer accepted by question author

Vijay A. Verma 104.8K Reputation points Volunteer Moderator
2018-08-01T17:30:03+00:00

Right formula is

=TEXT(L2,"dddd")

DAY argument needs to be removed.

=TEXT(DAY("8/1/2018","dddd")

=TEXT(1,"dddd")

is treated as =TEXT("1/1/1900","dddd")

Hence the answer is Sunday.

Was this answer helpful?

1 person found this answer helpful.
0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2018-08-01T17:32:26+00:00

    Thx for the explanation!

    Was this answer helpful?

    0 comments
  2. Anonymous
    2018-08-01T17:32:04+00:00

    Cool! Thx

    Was this answer helpful?

    0 comments