Home Archives for Calculate Dates in Excel
October 28, 2008 3 Comments

Viewer Request: How do I calculate dates in the future?

Today’s lesson is my answer to a viewer’s question: “How does Excel calculate a date in the future?”

The viewer wrote me because they were having trouble writing a formula that would return a date “N-Months” in the future. Or “3 years in the future” from a specific starting date.

I am asked this question frequently. In this short Excel video lesson I demonstrate how you write the formula.

Here are the steps to follow in this Excel Training Video:

  1. Use the AutoFill feature and Options to increment your “Starting Date.” I recommend using your “right-mouse button” to AutoFill because it automatically brings up a Menu of Options when you release the Right-Mouse Button.
  2. In response to the viewer’s question, we will choose “Fill Months.”
  3. Remember that we are using a “Hard-Coded” date as our “Starting Date,” so each value is really a “constant value.” This may not be the optimal result that we are seeking.
  4. In order to provide flexibility (with your starting date) you need to learn how to construct a formula that will increment (in this case the MONTH) our values.
  5. Use the “DATE() Function.
  6. For each of the “Arguments,” use the YEAR(), MONTH() and DAY() functions.
  7. To answer my viewer’s question we use MONTH(A2) +1 in the “Month Argument.”
  8. To “Increment the Year,” we use YEAR(A2) +1 in the “Year Argument.”
  9. Finally, write a “Formula” to verify that Excel is properly accounting for “Leap Years.”

Search My Index to all Excel Video Training Topics

The 50 Best Tips for Excel 2007″ DVD is now available for purchase. I invite you to visit my online bookstore for more details.

Link to this post!
Filed Under: Excel Date and Time Functions, Excel Training Tagged: , , , , ,
August 27, 2008 3 Comments

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:

  1. Dates, in Excel, are serial numbers. Serial number 1 is January 1, 1900. (Apple Macintosh computers use January 1, 1904 as serial number 1.)
  2. Use the Keyboard shortcut Ctrl+Shift+~ to reveal the serial number of any date.
  3. 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)
  4. 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.
  5. 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
  6. To calculate the # of Years between dates use a formula like; =YEAR(C7)-YEAR(B7)
  7. 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”)
  8. 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.
  9. 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.
  10. 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))
  11. 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))

If you have questions on this lesson, please send me an email danny@thecompanyrocks.com

Find the Excel Video Lesson that you want – Index to all Excel Topics

News! My DVD, “The 50 Best Tips for Excel 2007” is now availabe to purchase. I invite you to visit my online bookstore for more details.

Related Videos

Link to this post!
Filed Under: Excel Functions, Excel Training Tagged: ,