If this would be better suited over on the Mathematics Exchange, please move it appropriately. I thought I would start here for the history type aspect


I'm currently working as a Data Entry employee entering data with dates attached. In the process of entering data I also export and manipulate that same data for management, mostly into Excel. I've been a web author, programmer, consultant, tech support and everything in between, but as this data entry employee, I admit I'm fascinated by one item in Excel that I know was somewhat of a tough feature to program until I discovered how it was done, which has to do with why I'm writing this question.

Excel Feature of Interest to Me

If one types a date in Excel, and changes the Display format of cells, the date gets returned to include the day. Example (Choosing a date at Random):

  • Typing 2/15/2069 returns Friday, February 15, 2069
  • Typing 4/17/1503 returns 4/17/1503

Using 4/17/ as a baseline, it looks as if any date before 1900 is returned without the day:

  • Typing 4/17/1900 returns Tuesday, April 17, 1900
  • Typing 4/17/1899 returns 4/17/1899


I found the following congruence with the help of Google: Zeller's Congruence which listed the following formulas/algorithms:

  1. Gregorian Version

Zeller's Congruence for Gregorian

  1. Julian Version

Zeller's Conversion for Julian


  • h is the day
  • q is the numerical day in the month
  • m is the month from 3 to 14. (Moving Leap Year to the end makes it easier to compute)
  • k is the last 2 digits of the 4 digit year
  • J is the first 2 digits of the year
  • mod 7 ensures the result falls within the range for h, with 0 being Sunday and 6 being Saturday


If I understand WikiPedia correctly (13/5 = 2.6) accounts for the number of sets between 30 and 31 which is 5, and 13 would account for the months of the year minus February. (K/4) determines every leap year, along with (J/4).

  1. I'm having trouble comprehending why the centuries are doubled (2J) for Gregorian?
  2. Why does the Julian version need an offset of 5 minus the Century (+5 - J)?
  3. When did the Switchover actually take place. According to WikiPedia it was 1582, but the last Julian Calendar was in use in some places up until 1923. Am I safe to use the Gregorian Formula for every date between 1582 and now?
  4. Why doesn't Excel compute dates before 1900? What is the "cutdown" version they are using? (I'm assuming it has to do with the epoch)
  5. Why were the letters in the formulae chosen the way they were, i.e. do J and K have contextual meaning, or is this just the person's choice who wrote the Wiki entry?


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.