Excel - Calculating the weekday for a given Date
You can download the file to your computer either from:
or from:
The workbook's name is:
find weekday from date-English Version-6.xlsx
In the workbook, I juxtapose both solutions (mine and Microsoft's) so that you can see for yourself.
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:
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.
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"....)
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
https://www.dropbox.com/s/4unuqq8dp9ez48n/find%20weekday%20from%20date-English%20Version-6.xlsx?dl=0
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:
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
For example, Mr. Prem Beejan's solution where he bypasses Excel's shortcomings by adding 2000 years to the date...
For example, Mr. Prem Beejan's solution where he bypasses Excel's shortcomings by adding 2000 years to the date...
