Converting Numbers to Formatted Times

Date and Time data can come in many different formats.  This is particularly true when you are downloading records into Excel from other database programs, such as SQL, Access, Adabus, etc.

For instance, what do you do if your time data is expressed in an overly simplified and decidedly cold format?  For the sake of example, let’s say 531 would represent 5:31, 1112 would represent 11:12, and so forth.

Now any razor-sharp geek is likely not going to mind this austere method of characterizing time, but most typical end users of reports may not be so forgiving.  The solution, however, as illustrated below, is not too difficult.

By using an IF statement and some Text functions in Excel, a formula can be created to convert your bleak data into a user-friendly format.  Although at first glance the formula below may appear quite complex, if you take a moment and break it down into its parts, you will see it is not that challenging.

To save time, however, you can just copy the formula and plug it into your spreadsheet.  For this example, you would put it into cell B2, double-click the handle, and Voila, Time Conversion!

 

If you have 10 records or 10,000, they will all be converted at the click of a mouse.  Humans like consistency of data representation, so if you ever need to convert some somber time data, this may be just the ticket!