Calendar Calculator

There are many reasons why you might want to do some calendrical calculations. Maybe you want to convert a Gregorian calendar to its Julian date. Perhaps you need to the time between a specific timeframe for timesheets, scheduling meetings well in advance, or planning your wedding. You may have to know the exact day of the week for legal or tax purposes, but it’s nice to know how to do a calendar calculator.

Whatever the reason, here are some of the best ways to calculate dates on your calendar.

Calendrical calculations.

If you want to manually calculate the distance between dates, and don’t have a problem with mathematical formulas, here a couple of formulas to try out.

Zeller’s Rule

The method is called Zeller’s Rule, or Zeller’s Congruence. It was developed by German mathematician Christian Zeller as was to calculate any day of the week for either the Julian or Gregorian calendar.

Here’s the formula:

F = K + [(13xM – 1)/5] + D + [D/4] + [C/4] – 2C

  • K = Date. So, as an example, for 06/08/1990 K=06. It should be noted that the months starting from March.
  • M = Month no. Because the months start with March, March = 1, April = 2, and so forth. Going back to our example of 06/08/1990, M = 6.
  • D = The last two digits of the year. So, in for 6/08/1990 D=90. Also, as explained on Career Anna, “that when you have to find the day of the first or second month of any year, then Year=Given year-1.” For instance, if you wanted to find Day of 15-2-1990, it would be K=15, Month=12, D=Given Year-1=1990-1=1989=89.
  • C = The first two digits of the century. For 06/08/1990, C = 19.

Want to calculate the day for 06/08/1990? Using the formula above it would be: F = K + [(13xM – 1)/5] + D + [D/4] + [C/4] – 2C

When we replace the values in the formula then it we come up with: F = 06 + [{(13 x 6)- 1}/5] + 90 + 90/4 + 19/4 – (2 x 19)

That would mean that: F = 06 + 77/5 + 90 + 90/4 + 19/4 – 38

That comes out to: F =06 + 15.4 + 90 + 22.5 + 4.75 – 38

As also noted on Career Anna, make sure that you only consider the integral value and ignore the value following the decimal. If you were to remove the value after the decimal, it would look like this: F =06 + 15 + 90 + 22 + 4 – 38. As a result, F = 99.

Finding the day of the week.

After finding the numerical value for the day, divide the number by 7. You only need the remainder value. So, if we continue to use the example above, the remainder is 1.

You’ll want to match that with the following:

  • 1 = Monday
  • 2 = Tuesday
  • 3 = Wednesday
  • 4 = Thursday
  • 5 = Friday
  • 6 = Saturday
  • 7 = Sunday

So by Zeller’s rule, the 6th of August, 1990 was on a Monday.

If you’re not into mathematical formulas and need further explanation of Zeller’s Rule, I recommend visiting The Math Forum or Beginner’s Book.

The Key-Value Method

Another way to calculate the day of the week is through the Key-Value Method. It involves the following steps:

  • 1: Take the last two digits of the year.
  • 2: Divide those digits by 4 — discard any remainder.
  • 3: Add the day of the month to the value that you got in the second step.
  • 4: Add the month’s value key.

Let’s pause for a second here. You need to be aware of the month’s value key, which is:

  • January – 1
  • February – 4
  • March – 4
  • April – 0
  • May – 2
  • June – 5
  • July – 0
  • August – 3
  • September – 6
  • October – 1
  • November – 4
  • December – 6

If your date is in January or February of a leap year, subtract 1.

Alright, so let’s jump back, shall we?

  • Step 5: Add the year (century) code — 1700s = 4; 1800s = 2; 1900s = 0; and 2000s = 6.
  • Step 6: Add the last two digits of the year to the value from the previous step.
  • Step 7: Divide by 7 and take the remainder. This time, Sunday = 1, Monday = 2, and so on.

Here’s an example via Beginner’s Books for 19th November 2582:

  • The last two digits of the year, 82, are divided by 4. So, 82 / 4 = 20, with a remainder 2. Remember, we discard the remainder.
  • Add the day of the month. In our example, 20 + 19 = 39. Since we’re using November, the key value of 4. Therefore, 39 + 4 = 43.
  • Since the year 2582 isn’t in the table, we would add or subtract 400 until we have a year (century) that is in the table. So, 2582 – 400 = 2182. 2100 is also not in the table. So, subtract 2182-400=1782. Thankfully, the 1700s are in the table, and the code is 4. GO ahead and add this to the running total: 43 + 4 = 47.
  • Add the last two digits of the year to the value we just obtained in the previous — 47 + 82 = 129.
  • Divide this by 7 and take the remainder.129/7=18, the remainder is 3.

Here, the remainder is 3. In this case, it will be a Tuesday on 19th November 2582.

Finding a calendar for any given year.

“In any year, January 1 could be on any of the seven weekdays, and the year might or might not be a leap year,” explains Dr. Math on the Math Forum. “This means that there are only 7 * 2 = 14 possible ways to make a calendar that has every date listed under the appropriate day of the week.”

Knowing this, you could design “a bigger calendar that tells you which yearly calendar to use for any given year.” To make calendars to use for any given year is known as a perpetual calendar where “the pattern of weekdays in the Gregorian calendar repeats every four hundred years.” Repeated every 400 years is “because there are exactly 20 871 weeks in 400 Gregorian years. (100 * (365*3 + 366) – 3 = 146 097 days, and 146 097 / 7 = 20 871 weeks.)” Since this pattern repeats, “perpetual calendars only have to cover four hundred years.”

Calculating dates on Excel and Sheets.

Personally, mathematical equations make my head spin. In all seriousness, when I was in school, these were the type of things that gave me nightmares. Thankfully, if you’ve created a calendar using Excel or Google Sheets, then the programs make this much more manageable.

Excel

Calculating the number of days, months, or years between two dates in Excel is pretty simple. You need the formula to subtract the later date from the earlier date.

Francis Hayes, aka the Excel Addict, writes that “if cell A1 contains 1-Jan-2004 and cell A2 contains 03-Mar-2004, you simply enter the formula =A2-A1 in cell A3 to get the number of days.” What if the result was a weird date? Well, “that’s because Excel assumed you were entering another date and automatically formatted cell A3 as a date.”

You can correct that by heading over to the Home tab and clicking the Number Formats dropdown (in the Number group). Next, select General. Your answer should be 62.

Microsoft’s secret sauce.

Unfortunately, calculating the months and years aren’t as straightforward. But, Excel does have a Function that makes this task easy. The caveat? Microsoft has hidden this away.

So, what is this secret function? It’s called DATEDIF(i.e., date difference). And, here’s the syntax for this function: =DATEDIF(start date,end date,“interval”).

The “formula to calculate the number of complete months between the two dates would be:

=DATEDIF(A1,A2,”m”)

This formula can also be used to calculate the number of complete years, which is =DATEDIF(A1, A2, “y”). However, the example used above would yield 0 complete years. To correct this, change “cell A1 to a date a year or earlier, and you’ll see the result.”

Here’s what to keep in mind using this function:

  • “The start date must be less than or equal to the end date; otherwise it will give an error.
  • Acceptable interval codes are “d”, “m”, “y”, “ym”, “yd”, “md” (with quotes).
  • It may appear obvious what the “ym,” “yd,” and “md” interval codes do but they require a second look. The “ym” interval code yields the number of months between the two dates as if they were in the same year and ignores the year. The “yd,” and “md” interval codes yield the number of days between the two dates as if they were in the same year and ignores the year.”

Since Mr. Hayes is an awesome individual, he says to copy the following formula:

=DATEDIF(A1,A2,”y”) & ” years,” and DATEDIF(A1, A2,”ym”) and and “months,” and DATEDIF (A1, A2, “md”) & “days.”

Now you have to paste it into your formula bar. Don’t forget to adjust the cell references as necessary.

Google Sheets

If you’re over Excel, then you might have switched over to Google Sheets. It may not have as many features as Excel, but it does have something Excel doesn’t; it uses a subtraction function. A subtraction function means that you can deduct one date from another — just as long as both are in the same syntax.

Here it is said syntax: =MINUS (value 1, value 2)

Robert Hayes on TechJunkie explains that “To use MINUS, open a blank Google Sheets spreadsheet in your browser. Enter (as an example) ‘4/4/2017’ and ‘5/15/2017’ in cells B3 and C3.” Next, “select cell D3, which is where we’ll put the MINUS function.” Just click “inside the fx bar, and then input ‘=MINUS(C3, B3)’ and press Enter. Cell D3 will now return the value 40.”

As you can see, there are 40 days between 4/5/2017 and 5/15/2017. However, you can “also find the difference between the dates just by entering the cell references and not bothering with the MINUS function,” added Hayes. As an example, “click cell E3 and input ‘=C3-B3’ in the function bar.” Doing so “will also return 40, although since you are directly subtracting dates without MINUS, the value in cell E will probably display in date format and look very strange.” Additionally, you’re able to “convert the cell format to show an integer value by selecting Format > Number and Number.”

Another way to do this would be to “input the cell references with the earlier date first.” For example, if you put ‘=B3-C3’ in the function bar, then the cell would include the value -40. “This highlights that 4/4/2017 is 40 days behind 5/15/2017.”

Other Google Sheets Functions

Google Sheets also includes DAY36o. This “calculates the difference between dates for a 360 day year.” It’s used primarily for financial spreadsheets where “interest rate calculations might be required.”

“The syntax for DAYS360 is: =DAYS360 (start_date, end_date, [method]). The [method] is an optional indicator you can include for the day count method,” writes Hayes.

“To use this function to your Google Sheets spreadsheet for the dates 1/1/2016 and 1/1/2017, enter ‘1/1/2016’ in cell B4 as the start date, and then input ‘1/1/2017’ in C4 as the end date for the function.” After that, “select cell D4, input the function ‘=DAYS360 (B4, C4)’ in the fx bar and press Enter. Then cell D4 will include a total of 360 days between the selected dates. Note that the only real use for this particular function is if you are working with interest rates.”

There’s also a NETWORKDAYS function that will only calculate days between dates. The catch is that it only counts weekdays. Using only weekdays means that it doesn’t include weekends.

The syntax for NETWORKDAYS is: NETWORKDAYS (start_date, end_date, [holidays]).

Here’s how to use this function using the example dates 4/4/2017 and 5/15/2017 entered in cells B3 and C3 yet again.

Start by selecting “a cell to include the day total in, and click on the fx bar to insert the function. Next, “input ‘=NETWORKDAYS(B3, C3)’ and press the Enter key to add the function to whichever spreadsheet cell you’ve chosen for it. The NETWORKDAYS cell will include the total 29 for the number of days between the dates,” explains Hayes.

If you want to add a holiday, enter ‘4/17/2017’ in cell A3. Next, “select the NETWORKDAYS cell, click the fx bar, and modify the function by adding the cell reference A3 to it.” This would place the function in =NETWORKDAYS(B3, C3, A3), which will return 28 with the extra holiday also deducted from the total days.

Bonus: Getting Outlook to Count the Days Between Two Calendar Dates

Outlook can get complicated sometimes. But, there are moments when it can surprise you. And this is one example.

Log into your account and go into your calendar. Next, click the “Home” tab and then “New Appointment.” This will open the Appointment window. Now, enter a subject in the “Subject” text box, obviously, and input the following: “Start Time” text box: 21 days after tomorrow. After pressing “Enter,” you’ll notice that Outlook will compute the number of days between the current date and the future date — the future date will be displayed in the “Start Time” text box.

Since Outlook is well aware of holidays, you could use this function to find out when 12 days before Christmas are or 42 days after the Fourth of July.

If you’re an Apple user, you might want to install an app like Days between Dates.

Online Date Calculators

If you’re crunched for time, and really can’t stand formulas, you can always take the more natural path by using the following online date calculators:

Tags: ,