How To Work With Dates In Excel

As a former programmer, I use the term “Julian Date” to mean any serial date integer from any arbitrary beginning, but usually from the beginning of the 20th century. The Excel date code uses 1/1/1900 as the starting date as does Google Sheets.

The date is displayed in an m/d/y format. You enter the date and the spreadsheet displays it as entered while it stores it as a value equivalent to the number of days since 1/1/1900. Hence the term Julian Date.

Since dates are stored as a number of days since 1/1/1900, it is easy to compute the number of days difference by subtracting one date from the other. It is equally easy to figure when a future date will be such as 90 days from now by just adding 90 to the current date and format the resulting cell as a date.

When calculating the number of years between two dates in a spreadsheet, it is typical for one to compute the difference in days between the dates and divide the result by 365.25. However, the actual number of days for one year is approximately 365.2422 days.

Some History

The Julian calendar became official in 46 B.C replacing calendars that assumed 365 days per year. The Julian calendar corrected most of the problems by providing for a leap year with an extra day every four years, or an average of 365.25 days per year. This still creates an error of eleven minutes per year. By 1582 A.D., the Julian calendar had become out of step with the seasons by ten days. Pope Gregory XIII ordered that ten days be subtracted from the calendar and came up with the calculation that is used today.

By 1751 another eleven days had to be dropped, so the day after September 2, 1752, became September 14, 1752. An update of the leap year calculation was placed into law by an act of the British Parliament in the Calendar Act of 1751.

Because of these adjustments made over the years, if you ever need to calculate dates far into the past, these fundamental shifts have to be taken into consideration.

The new law stated that if you can divide a year by 4 evenly, it is a leap year. If it is a turn of the century year, it is not a leap year unless you can divide it by 400 evenly, in which case it is a leap year.

As most have known, if a year (all four digits) is divisible by 4 evenly, it is a leap year. 2020 divided by 4 is an even 505 and therefore a leap year with February having 29 days instead of 28.

In the year 1999, most programs thought 2000 would be a leap year because it could be divided by 4 evenly. But the rule did not apply to a turn of the century year. Therefore, during the Y2K issues in 1999, many programs were found to be incorrectly calculating the year 2000 as not a leap year when in fact, since it was divisible by 400, it was. This was the first year to come under that rule created in 1582. The next occurrence will not be until the year 2400.

Using The DATEDIF Function

In spreadsheets such as Excel and Google Sheets, the DATEDIF function is used to accurately calculate the number of days, month, and years between two dates. This is a function with the following syntax:

=DATEDIF(start_date,end_date,“code”)

1. The start date must be less than the end date and not less than 1/1/1900. If a date less than this is entered, the function will assume 1/1/1900 as the start date
2. The end date could be today’s date using the now() function
3. The code must be in quotes: “y”=years, “m”=months, “d”=days

Some Date Arithmetic

How old are you? Subtracting your birth date from today’s date results in the number of days between the two dates. Since there are 365 days in a year, you might think you can just divide the number of days by 365 to get the correct answer. Well, it will be close, but not correct. Every 4 years is a leap year (see above for the end of century rules) so it works better to divide by 365.25. However, even that is not totally accurate.

Note: The spreadsheet can only do date arithmetic on dates from 1/1/1900 on. Prior dates are not converted to a date code (Julian date). 

Note that I used the =TODAY() function to arrive at the current date.

Ctrl+; will enter today’s date into a cell. The Today function will use the current date every time the spreadsheet is open.

Subtract the DOB from Today to get the number of days difference.

Dividing the number of days by 365.25 results in an approximate number of years.

Instead, let’s try the DATEDIF function.

The first argument is the starting date, the second the ending date, and the third the desired results. Y=years. M=months. D=days. (or lower case y, m, d)

I hope you found this interesting and useful. The functions used above are available in Excel and Google Sheets. I have not tried them in any other spreadsheet program. Add it to your toolbox.

Dick

—