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!

The Mouse Rules! (Sometimes)

If you have been a reader of this blog for any length of time, you know that I am a big advocate of using Keyboard Shortcuts in Excel.  That being said, there are certain times when The Mouse Rules!

Let’s take a look at Five Great Shortcuts using the mouse in Excel:

Move Selected Cells by Dragging the Border

1.      Select a range of cells

2.      Drag the thick border line to move the cells

3.      Celebrate that this is faster than keyboard shortcuts!

Autofit Column Widths

1.      Select the columns you want to adjust

2.      Double click on the column letter separator line

3.      Bamm!  Perfect Fit!

4.      Bonus!  You can also use this technique to adjust row heights

Alt+Click for Stock Quotes

1.      Enter a company stock symbol in a cell (SBUX for Starbucks sounds good to me at the moment…)

2.      Alt+click the cell to launch the research pane

3.      Convenient way to follow your favorite stocks!

Select Non-Contiguous Ranges

1.      Use Ctrl+Click to select non-contiguous cells

2.      Many uses, such as special selections for formulas, formatting, editing

3.      How Cool is that!

 Grab the Handle for Autofilling

1.      Select a cell or range of cells

2.      Grab the Handle (small black square at the lower-right

3.      Drag horizontally or vertically to autofill the data (works on numbers, days of the week, and even Custom Data such as Week 1, Week 2…, Employee 1, Employee 2, etc.)

4.      Bonus: If your range is aligned with an adjacent range, simply Double-Click the handle!

 Yes, Keyboard shortcuts totally Rock, but sometimes The Mouse Rules!

Excel Uses by Real People

The versatility of Excel lends itself to nearly endless applications.  As I am sure most of us would agree, the potential uses are only limited by the user’s imagination.

In a very large recent poll, real people were asked what they use Excel.  Hundreds of responses were obtained, and many of them are quite interesting.  Some of them may, in fact, be inspirational.  Here is a sampling of 20 of the more interesting ways Real People have used or are using Excel:

 

1.      Tracking pupils' attainment and progress

2.      Tracking personal weight and blood sugar levels
3.      A version of a Boggle board
4.      Reservation databases displaying room availability
5.      Calculating the stability of material on the deck of a ship
6.      Queue calculator for medical patients
7.      Solvers for word puzzles
8.      Timesheets and labor calculations
9.      Closing quote data for over 1000 companies
10.   Randomly generating passwords
11.   Tracking sports data
12.   Recreated the format of a popular quiz show
13.   Managing meeting rooms availability
14.   Pricing engine for tour groups
15.   Genealogical research
16.   Tracking weight loss
17.   Video teleprompter for giving presentations
18.   Recipe scaling template
19.   Managing personal academic studies
20.   Creating attractive custom labels

Fascinating, isn’t it?  If you have any unusual ways that you have used Excel, I would love to hear about it.  Send me a note at ExcelEnthusiasts@gmail.com.  All the best!

Round like a Circle

On the face of things, Precision seems to always be preferable.  For instance, 12.324 is more precise that 12.3.  There are times, however, when precision is Not desireable, and may even not make much sense.

For example, let’s say you are staffing for a project, and you have calculated that you need 11.92 people to complete the job.  Clearly, you can’t find that .92 person (although one of my ex-brother-in-laws comes to mind…), so you Round up to 12.  The same may be said about currency, as fractions of a penny may not produce valid data.

 

There is always formatting in Excel that will make your numbers Appear to be rounded, of course.  If you are dealing with numbers representing currency that have four decimal places, you would obviously want to express the results in no more that 2 decimal places, so you can format for showing exactly that!  The trouble with relying on formatting, however, is that Excel retains the precision in the background, and any calculations will be based on that precision. 
 

So what do you do?  (Trumpets please…)  Use the ROUND function!  The syntax for the the ROUND function is:  ROUND( number, digits )

Four Examples are in Order:

In the worksheet below, Column B contains the rounding functions:

1.      B1 has:  =ROUND(A1, 2) and therefore rounds to two decimal places.

2.      In B2, =ROUND(A1, 0) is being used to round to zero decimals.

3.      In B3, more extreme rounding is being achieved by using =ROUND(A3, -1)

4.      B4 uses the same function as B3 and applies it to B4:  =ROUND(A4, -1)

Note:  If you always want Excel to round up, you can use the ROUNDUP function.  In our illustration, this would result in B3 being 30 rather than 20.  The ROUNDDOWN function does (no surprise) just the opposite.
 
Sometimes Precision is the enemy.  In such cases, use ROUND!