Learn how Excel calculates dates

If you create invoices or track time-sheets, you use date calculations in Excel. If you earn interest from an investment or pay interest on a loan, you need to understand how Excel handles dates. Once you grasp the concept that a date is a serial number, then you will start to understand how spreadsheets calculate dates. In this lesson, Danny will demonstrate the key concepts that will enable you to work smarter when you use dates in Excel formulas.

These are the steps to follow in this video lesson:

Dates, in Excel, are serial numbers. Serial number 1 is January 1, 1900. (Apple Macintosh computers use January 1, 1904 as serial number 1.)
Use the Keyboard shortcut Ctrl+Shift+~ to reveal the serial number of any date.
The =TODAY() function is very useful. It will update to reflect your system’s date. If you want to “date stamp” a cell, use the keyboard shortcut Ctrl+; to enter today’s date (this will not update)
Many formulas use the =DATE() function. To understand this Function, experiment with the =YEAR(), =MONTH() and =DAY() functions. These functions are frequently used inside the =DATE() function.
For the Formula =End_Date-Start_Date, decide if you worked, (earned interest, paid interest, etc.) on either date. If so, the formula is =End_Date-Start_Date+1
To calculate the # of Years between dates use a formula like; =YEAR(C7)-YEAR(B7)
Excel has a “secret” formula =DATEDIF() – you can not get help for this formula. Use it to determine a person’s age, e.g. =DATEDIF(B11, C11, “y”)
To determine the “Day in the year” use this formula =B15-DATE(YEAR(B15), 1, 0) – where cell B15 contains the date that you want to use.
To see how many day remain in the year, use this formula =DATE(YEAR(B19), 12, 31)-B19 – where cell B19 contains the date that you want to use.
When you want to write a formula to increment cells by 1 month (assuming that the starting date will change, use this formula =DATE(YEAR(F16),MONTH(F16)+1,DAY(F16))
When you want to write a formula to increment cells by 1 year (assuming that the starting date will change, use this formula =DATE(YEAR(F17)+1,MONTH(F17),DAY(F17))