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!

Imaginary Numbers Revisited

I am guessing I know what you are thinking:  Imaginary Numbers are something as useful in the real world as the stories of Alice in Wonderland (or, more accurately, “Alice’s Adventures in Wonderland”).

Although most of us seldom or ever run into any reference to Imaginary Numbers, there is a small faction of individuals in the sciences, such as electrical engineering, that could not succeed well without them.  The analysis of AC circuits, for example, would not be possible without these oddities in the math world.

If you are involved in an academic Math Curriculum (student or instructor), however, you will almost certainly encounter and use Imaginary Numbers at some time during your studies.

For those of us who have not had a math class in a long while, (or if you are not a Math Geek like me), a brief review of Imaginary Numbers is in order. They are simply a number that can be written as a real number multiplied by the Imaginary Unit i. The Square of i is -1. An imaginary number has a negative or zero square. For example, 5i is an imaginary number, and its square is -25.

In terms of imaginary numbers, the Square Root of -9 is 3i.

They are nothing at all new, as they were conceived in ancient Greece by a brilliant mathematician and engineer by the name of Hero of Alexandria. “Hero” (that’s quite a name, isn’t it?...) is noted for many inventions, including the first-recorded steam engine and a rocket-like reaction engine.  Not bad a first-century Greek.

So, what about Excel?  Interestingly, Excel can easily handle these oddities of the math world. It even has a couple of specially designed functions to deal with Imaginary and Complex (expressed in the form a + bi, where a and b are real numbers and i is the imaginary unit, where i2 = −1).

The Imaginary function operates with the syntax:
   =IMAGINARY(inumber)

The Complex function uses this syntax:
   =COMPLEX(realnumber, inumber, [syntax])

There are also several built-in tools in Excel for using imaginary numbers with typical arithmetic functions of addition, multiplication, etc.

So, why am I even discussing this topic today?  Well, if you have children in school, I wouldn’t be surprised if your son or daughter comes home one day with homework involving Imaginary Numbers.  Wouldn’t it be cool if you could show them how this can be done in Excel?  It may even be a surprise for her or his teacher! 

Imaginary Numbers.  Not just topics for Alice and the White Rabbit...

Top Ten Favorite Shortcuts for 2015

It’s been about two years since I touched on Keyboard Shortcuts, so “The time has come, the Walrus said, to talk of many things” (to quote Lewis Carroll):

As I have told have told hundreds of Excel students of mine over the years, Keyboard Shortcuts can add speed, efficiency, relief from the stress of overusing the mouse.  Although Carpal Tunnel syndrome is not as prevalent among Excel users as once thought, it is still a concern, especially if you are a woman (women contract the syndrome three times more often than men…).

These shortcuts are just plain Cool, and have no doubt about it, they will set you apart from Mere-Mortal Excel Users.

So, in Reverse Order, here are my Current Favorite (and highly recommended) Top Ten Excel Keyboard Shortcuts for 2015:

10.  ALT+F1 - Creates an Embedded Chart of the selected data (Always a crowd pleaser!)

 9.  CTRL+H - Find and Replace (I can’t imagine working without it!)
 8.  CTRL+K - Displays the Insert Hyperlink dialog box for new hyperlinks (Very useful for mimicking websites in your workbooks!)
 7.  Shift+F10 – Opens the right-click menu (Handy when your fingers aren’t on the mouse…)
 6.  CTRL+SHIFT+% - Applies the Percentage Format with no decimal places (Just how I like it!)
 5.  CTRL+9 - Hides the selected rows (Cleans up your worksheet, and it simply Cool!)
 4.  CTRL+: - Time-Stamp your worksheet (Good for time management…)
 3.  SHIFT+TAB - Moves to the Previous Cell (Once you get used to it, you will use it!)
 2.  F5 - Brings up the Go TO dialogue box (Great for navigating to a named range!)

And my Current #1 Favorite Keyboard Shortcut is (Drumroll please…):

 1.  CTRL+PageUp - Jumps to the previous worksheet (CTRL+PageDown jumps to the next worksheet) 

 
Try a few a few of these shortcuts sometime, and see how they can make your Excel Life Better!

The Red Light

Nothing grabs your attention like a Red Light.  (At least, we hope that’s the case when you’re driving.)  Cold, hard data in Excel can be, well, Cold.  Numbers can be rather numbing and even the best analyst may have moments when she or he has lapses in attention.

Since Excel 2007 was introduced, there is a small, but powerful tool available in Excel that is in my estimation considerably underused.  As we all know, Conditional Formatting can help focus the attention of the report user, but after all these years, merely coloring the cell background or font can be rather drab.  That is why I embrace the comparatively new Conditional Formatting Icons. 

For instance, in the table below, a Red Light Icon appears in the Alert column whenever the monthly sales were less than 20,000 (assuming that is the sales goal).

Here is How to Set this Up:

1. Select the range of cells in which you want a Semaphore to appear the (Alert column in the example)
2. Insert a simple IF formula that returns a “1” or a “3” (i.e. IF(C4<20 1="" 3="" b="">) Note: By default, a “1” will be a Red Light and “3” will be a Green Light  
3. Click Conditional Formatting on the Home tab, choose Icon Sets and pick the set that includes the Semaphore
4. Go back to Conditional Formatting and choose Manage Rules
5. Click Edit Rule and put a check in the Show Icon Only box and Apply

 Your Alert field will instantly take on the engaging Stop/Go formatting that will be sure to catch the attention of any of the report users.  Very Cool!

NETWORKDAYS

If you are in the position of Scheduling Projects or managing individuals’ time, knowing the number of Workdays between two dates can be highly important.  Knowing how many business days lie ahead is obviously essential for planning, as is calculating the number of days/hours that have been worked on a project.  

Whereas there are specialized types of software for managing projects, (such as Microsoft Project), Excel can be very useful in this arena.

If you have a set of dates in Excel and you want to calculate the number of Business Days, (excluding weekends and holidays), you can easily do this using the NETWORKDAYS function with the following syntax.  Linking the Dates with Absolute References to cells containing this information is a best practice:

=NETWORKDAYS(StartDate, EndDate, ListOfHolidays)

Similarly, let’s say you have a Start Date for a project and you have a budget of a certain number of workdays to spend completing it.  To calculate the Date that it will be completed, you can easily use the WORKDAY function. Applying the following syntax, you can quickly calculate When the project will be complete:

WORKDAY(StartDate, #OfDays, ListOfHolidays)

Another Best Practice:  Since Excel can occasionally misinterpret (or not understand) some imported data, you can use an additional function to ascertain that the “StartDate” is interpreted correctly.  By using the DATE function in the place for StartDate, you alleviate any concern in this regard.

Calculating workdays with the NETWORKDAYS and WORKDAY functions.  More key tools brought to you by Excel.  Cheers!