The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,616 other followers

Excel worksheet function – How do I get the weekday name of a date?

Posted by jpluimers on 2014/07/04

At first sight you’d think that getting the weekday name of a date in Excel is as easy as this simple example using the WEEKDAY function as an intermediate:

A1 cell: 1/8/2009
B1 cell: =TEXT(WEEKDAY(A1),"dddd")

This will, for the given date, print the corresponding day.

The outcome for the 1st of August 2009 (we do dd/mm/yyyy over here) is saturday, and you might think it is the right way to do it.

Well, as AdamV explains, it is not: the outcome is OK on certain systems, but not on all systems.

The actual solution is even simpler, but before we go there, lets first explain what is potentially wrong with the above code:

  • A1 has a date value
  • =WEEKDAY(A1)
    obtains an integer value
    in this case: 7
  • =TEXT(7, “dddd”)
    obtains the weekday name of the integer value 7
    in this case: SATURDAY
  • The latter is only because of two things:
    • First the way TEXT operates:
      =TEXT(B1,”dddd, yyyy-mm-dd”)
      returns this full date:
      Saturday, 1900-01-07
      Which means that if you don’t fill in a month or year, it uses January of 1900.
    • Second:
      Excel thinks the 1st of January 1900 is a Sunday (actually, it is Monday)
      so for Excel, the 7th of January 1900 is a Saturday.

Excel has a bug where weekdays before the 1st of March 1900 are wrong (it is the famous 1900 problem, which somewhat is the inverse of the – also famous – 2000 is not a leap year problem) which won’t get fixed as Excel wants to stay compatible with Lotus-1-2-3 which also has the bug.

So it is by luck that Excel gets the above way right.

To make sure it is always right, just format your date as “dddd” as AdamV suggests:

=TEXT(A1,"dddd")

This is much shorter than the first example, and always works well.

–jeroen

via: worksheet function – How do I get the day name into a cell in Excel? – Super User.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: