## יום שישי, 27 ביולי 2012

### Excel - Calculating the weekday for a given Date

Excel - Calculating the weekday for a given Date

This post is intended for English speaking visitors to my blog, who can't read Hebrew…
I've developed a formula which calculates the weekday of any given date (from: 01/01/1600 to: 31/12/9999)

Question: "Excel" already has a built-in function which calculates the weekday for a given date. This is the WEEKDAY function.
So why "reinvent the wheel"? KKK

Answer: the WEEKDAY function has 4 flaws which my formula solves:
1. It can't calculate dates prior to 01/01/1900.
2. In calculation of dates between 01/01/1900-28/02/1900, it displays wrong results.
3. Excel has another "bug": it considers the 29/02/1900 as valid date (This is due to a known "bug" in Excel, which wrongly assumes that the year 1900 was a leap year).
4. Using Excel's built-in function: WEEKDAY is quite cumbersome. The function only returns a number (i.e., 1 - for Sunday, 2 - for Monday...., 7 - for Saturday). So, if you want to display the date's name you have to either custom format the cell (for example: "[\$-409]dddd" to display the full name's day ("Sunday", "Monday"....) or "[\$-409]ddd" to display the day's name shorter format ("Sun", "Mon"....)
LLL

or from:

2) Dropbox

The workbook's name is:
find weekday from date-English Version-6.xlsx

One has to key in the date (day, month, year) as follows:
Enter day (a number between 1 and 31) in cell L1
Enter month (a number between 1 and 12) in cell L2
Enter year (a number between 1600 and 2400) in cell L3

The Result (weekday) will appear in cell H9.

In the workbook, I juxtapose both solutions (mine and Microsoft's) so that you can see for yourself.

Notes:
PaxMundi@gmail.com
and I'll send you the file.
2. The formula has been checked in the following Excel versions:
2007, 2010, 2013.
3. The algorithm is explained in the Hebrew version of this post. The best explanation in English can be found here:
http://5dspace-time.org/Calendar/Algorithm.html
HMy algorithm is based on an algorithm developed by....Lewis Carrol, the famous 19th Century author, mathematician and logician, whose most famous book was "Alice in Wonderland".

Martin Gardner's book: The Universe in a Handkerchief, Lewis Carroll's Mathematical Recreations, Games, Puzzles and Word Plays
http://www.amazon.com/Universe-Handkerchief-Carrolls-Mathematical-Recreations/dp/0387256415/ref=sr_1_1?s=books&ie=UTF8&qid=1420923828&sr=1-1&keywords=martin+gardner+the+universe+in+a+handkerchief

4. I realize that there are simpler, shorter solutions.
For example, Mr. Prem Beejan's solution where he bypasses Excel's shortcomings by adding 2000 years to the date...