יום שישי, 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

You can download the file to your computer either from:
1) Google Drive

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:
1. If you can't download the file, please send me a request via e-mail:
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:

4. My algorithm is based on an algorithm developed by....Lewis Carroll, the famous 19th Century author, mathematician and logician, whose most famous book was "Alice in Wonderland".
You can read about his algorithm in:

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


5. 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...






אין תגובות:

הוסף רשומת תגובה