The Wiert Corner – irregular stream of stuff

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

  • My work

  • My badges

  • Twitter Updates

  • My Flickr Stream

    20140508-Delphi-2007--Project-Options--Cannot-Edit-Application-Title-HelpFile-Icon-Theming

    20140430-Fiddler-Filter-Actions-Button-Run-Filterset-now

    20140424-Windows-7-free-disk-space

    More Photos
  • Pages

  • All categories

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

    Join 1,734 other followers

Archive for July 4th, 2014

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.

Posted in Excel, Office, Power User | Leave a Comment »

 
%d bloggers like this: