Home Archives for Text-to-Columns Data Tool
October 27, 2012 1 Comment

Learn How Excel Stores and Formats Dates

Formatting Options for Dates in Excel

Multiple Formatting Options for Dates in Excel

In my experience, a high percentage of Excel users run into problems when working with Dates – and Times! In fact, I estimate that, during the past two weeks, I received 25 questions from viewers – they were asking for my help when working with Dates in their Excel workbooks.

Here, in this tutorial, I gathered together the most common problems that my viewers asked me help them to solve.. Gaining a firm understanding of how Excel Stores and Formats Dates is an Essential Skill that you must master!

Dates Stored as Serial Number

Dates are stored as a serial number in Excel. For example, if you type the number 1 into a blank cell and then change the formatting to a Short Date, you will see 1/1/1900 displayed. This is the date on which Excel began to keep track of time.  Dates that are entered correctly align to the right side of the cell.

Concepts Covered in this Tutorial

  • Use the =TODAY() Function – this date will automatically update.
  • Use the Keyboard Shortcut Ctrl + ; (semi-colon) to “Date Stamp” a cell – this is a static date.
  • Change the Formatting of a Date to e”General” – this reveals the actual serial number that Excel stores for the Date.
  • Apply Custom Date Codes to format cells that contain Dates.
  • Learn why it is essential that you always enter years using four digit codes – to avoid “the Century Break.”
  • Learn how to clear all formatting from cells before pasting or entering data. in them
  • Use the Text-to-Columns Wizard to convert “unfriendly” Date Text Labels to “usable” Numeric Date Values .
  • Tips for copying and pasting a range of cells formatted as USA Short Dates to a customized “European” Date Format.

Visit My Secure Online Shopping Website

I invite you to stop by my secure online shopping website – http://shop.thecompanyrocks.com – to take a look at all of th many training resources that I offer to you.

Watch Video in High Definition

Follow this link to watch my Excel Tutorial in High Definition on my YouTube Channel – DannyRocksExcels

Podcast Video [ 11:03 ] Play Now | Play in Popup | Download (4288)
Link to this post!
Filed Under: Essential Excel Skills, Excel Basics, Excel Training Tagged: , , , , , ,
April 3, 2011 Leave a Comment

How to Search For and Use Text Strings in Excel

A viewer asked for my help in creating a report that will group his customers’ email addresses by their “domain name.” For example, he wants to be group all customers with gmail addresses . Likewise, his  customers’ yahoo mail, aol.com accounts, etc.

Pivot Table for Email Domains

Pivot Table Email Domains

I immediately decided to use two powerful Excel tools to produce this report:

  1. Text-to-Columns to create two columns (name and domain) from one text value (the email address).
  2. Pivot Table Report to group and present customer emails by domain name.

Both tools work great and I produced the report with only a few mouse clicks!

The response that I got from my viewer was, “That’s great Danny. But …”

It turns out that the viewer wanted to have the Domain Names as the Field Headers and to show each customer’s email address as a row going down vertically in the corresponding field. To give my viewer the report that he wanted, I used these tools and techniques:

  • I returned to the Pivot Table to copy the unique list of domain names.
  • I used Paste Special – Transpose to convert the vertical list of domain names into a horizontal row of Field Headers.
  • I began to build my “Mega-formula” by taking “baby steps” – i.e. I created one formula and then “nested” it inside additional formulas and functions.
  • For the first “baby step” formula I used the SEARCH() Function.
  • Next, I “nested” this formula as the “logical test” inside an IF() Function.
  • In order to “hide” all of the formula error signs, I nested both of these functions inside the NEW IFERROR() Function.
  • IFERROR() was introduced in Excel 2007 and I now consider it to be “indispensable!” This function, alone, makes upgrading to either Excel 2007 or Excel 2010 a “nobrainer” decision!

I enjoy answering questions from my viewers. Many of these questions can be answered from one of the lessons on my DVD-ROM, “The 50 Best Tips for Excel 2007.” For the rest, I try to create a video lesson that demonstrates how to get the answers that you – my viewers and customers – are looking for.

Keep your questions and suggestions coming! I enjoy receiving feedback from you!

I invite you to subscribe to my RSS Feed on Feedburner to be automatically notified whenever I publish a new Excel Video Tutorial.

Watch this Excel Video in High Definition on YouTube

Here is the link to watch this video on my YouTube Channel – DannyRocksExcels

Searching for Text Strings in Excel [ 9:36 ] Play Now | Play in Popup | Download (1139)
Link to this post!
Filed Under: Excel Training, New in Excel 2007, Pivot Tables, Podcast Tips & Timesavers, Text Functions Tagged: , , , , , , , , , , ,