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!

 

 

Flash Fill

It isn’t often that I get really excited over a new Excel feature, but this one is The Cat’s Pajamas (to use an old phrase from the 1920s…)!

Flash Fill enables the Excel 2013 user to Reformat lists of information with remarkable ease and speed.  This fabulous new tool was first introduced in Excel 2013, and automatically fills your data when it senses a pattern.  It works with lists of names, numbers, and a variety other data.

For Example, we will first look at how it works with names.  Let’s say you have imported a list of names that are in the format of Last Name, First Name with each name entirely in one cell.  For this example, we will assume that you wish to convert these names into two fields, with the first field being just the First Names and the second field just the Last Names. 

With past versions of Excel, you could do this by using somewhat complex formulas that are a bit intimidating to many users.  With Flash Fill, however, it is amazingly simple.  For our example, all you need to do is type the first name of the first entry in the field adjacent to your list, and go to Fill / Flash Fill on your Home ribbon.  Excel does the rest by automatically filling in the entire field of first names!  You can then extract the last names using the same approach.

Alternatively, you can just start typing the second first name in the new field, and Excel will sense the pattern and suggest filling the entire range (no need to go to Fill / Flash Fill).

One More Example:  Let’s say you have a field of Social Security Numbers that are not formatted with hyphens.  Simply type the first SSN (with the hyphens inserted) in the field next to the list, and use the same technique as we used for the names.  Voila!

 Flash Fill.  It’s Lightning Fast!

Random Ideas

Among the many obscure functions in Excel is the useful and versatile RAND function. Each time your worksheet recalculates, (by reopening or forced recalculation by pressing F9), the RAND function returns a new Random Number that is greater than or equal to 0 and less than 1.  The syntax for the Rand function is simply:  =RAND()

So, what are some thoughts on using this interesting function?  Well, how you use it is only limited by your imagination, but here are a few examples of Random Ideas:

·       Use =RAND()*100 to return random numbers between 1 and 100

·       Or =RAND()*100 + 100 to return random numbers between 100 and 200

·       If you want only whole numbers, use:

     =INT(RAND()*100+100 to return integers between 100 and 200

Or consider this, let’s say you have a database of 1,000 records from which you want to take a random sample of 15% of the data.  If your data is, for instance, in Column ‘A’, put in the following formula in Column ‘B’, and Autofill it to the end of data field:

=RAND()<=0.15

Then simply sort your columns based on Column B and retain the first 150 of your data field.  This will, of course, change every time the worksheet is recalculated because RAND is, well, Random!

Some extremely clever Excel Enthusiasts have created some amazing games using RAND.  For my own part, (modest in comparison to what some others have done), I have used the RAND function (along with other functions and graphics), to create a Vegas-style Slot Machine in Excel.  If you are interested, please send me a request at ExcelEnthusiasts@gmail.com if you would like a copy of the Slot Machine workbook, and I will be happy to send it to you.

By the way, if you have any RANDom Ideas regarding this cool function, I would love to hear about them!

Converting to British Metrics

As with many technological advances that preceded it, it is axiomatic that the Internet has furthered the effect of the world getting smaller.  Mastery of different systems of commerce and communication has become all the more important as we evolve at light speed.  The fact that hundreds of Excel users in the United Kingdom access this blog each month is a minor testament to our interconnectivity. 

With the many websites devoted to exchanges, it is a rather simple matter to Convert from One System of Measure to Another.  If you are working in Excel, however, there is no need to access any outside tools for this purpose. The built-in CONVERT function can handle most of our needs in this regard, and is one of those obscure little tools in Excel that you may find to be very convenient.

 

The CONVERT function can translate a Wide Variety of measurements that include Time, distance, Temperature, Weight, and much more.

Using the appropriate abbreviations for the Units, the Syntax of the CONVERT function is as follows:

CONVERT(Number, From Unit, To Unit) where:

1. Number is the Amount in From Units to convert
2. From Unit are the units Being Converted
3. To Unit are the Units in the Result

Some Commonly Used Abbreviations are shown in the table below.  For a Full List of all the measures that can be converted, please see the CONVERT function Help topic.

Meter	“M”
Inch	“in”
Foot	“ft”
Yard	“yd”
Degree Celsius	“C”
Degree Fahrenheit	“F”
U.S. Pint	“us_pt”
U.K. Pint	“uk_pt”
Gallon	“gal”
Imperial Gallon	“uk_gal”
Liter	“L”

The CONVERT function.  Another one of the Little-Known Gems in Excel that can make your life just a bit easier.  Give it a try some time…