Tuesday, March 31, 2015

Time, Time, Time

Ever since the early beginnings of Excel, handling Time in this program has caused many people a great deal of, well, time and grief.  This is especially true when it comes to data which has been Imported from some other program (usually a large database handling system).

There are all sorts of anomalies that can bedevil Excel users, but this week we are going to look at the common instance of when data is imported as text, And the application of one particularly Cool Trick! 

When you import the data you are going to be working with in Excel from such Database Software Systems such as Oracle, SQL, SAP, or others, you will often have the time data in the form of Text.  It cannot, therefore, be manipulated in Excel with sorting, functions, etc.  But how do you know if this is the case?

One giveaway that the new time data in your Excel worksheet is in text format is that the Time (or what appears to be “Time”) is aligned on the left-hand side of the cells into which it was imported.  If it was an Excel-friendly time-format it would be aligned on the right-side of the cells.

Another way to determine if the cells in question contain text is to use the built-in ISTEXT() function.  By placing a new column next to your Time column, you can insert this function and quickly copy it down your worksheet.  If the results come back as TRUE, your data is in the Text format.

So how do You Change Your Data from Text Format to Excel-friendly Time Format?  Easy:
  1.   In a cell outside the database, (but on the same worksheet), put the Number “1” in it, and format it the same as the cells in the database
  2. Then copy the new cell with 1 in it, and select the column with your suspect Time data
  3. Next use Paste Special – Multiply (Great old trick…) on all of the “Time” data, converting it to Excel-Friendly numbers
  4. Then simply configure the newly amended Time data into the desired time format
Badda-Bing, that’s it!  Converting your Text-based-Time to Excel-friendly-Time couldn’t be much easier.  Give it a try!

1 comment:

BobJordanB said...

To test if a cell has a true time in it, try adding 1 to the time - it should give tomorrows date and time but will error if it finds text. I just create a text column =B1+1 where B1 is the time value to be tested.