Use Excel’s Text-to-Columns Tool to Quickly Correct Date Entries

The Text-to-Columns tool in Excel is one of my favorites. I use it to quickly “parse” text elements in a cell. For example, to separate “First Name” from Last Name in a cell that contains the “Full Name” entry.

In this short – 3 minute, 23 seconds – video, I demonstrate another way to use Text-to-Columns. I show you how to separate the “Year,” the “Month” and the “Day.” A client called me in a panic – they downloaded daily transactions from their mainframe computer into Excel. However, the “dates” appeared as – e.g. 20100901 – and they could not find a way to format the dates as – e.g. 9/1/2010

Learn how to “Master Excel in Minutes – Not Months!”

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.