Excel - Inserting the current Date and/or Time

When using Excel, there is often the need to enter a time, a date or even a combination of the two. Providing this data is entered is a recognised date/time format, Excel will treat the entered data as a value.

A quick way of entering the current date and/or time (providing they are set-up correctly on your PC) is to use the following short-cut key combinations;

To enter the current date (as set on your PC)

Ctrl key plus semi colon

<Ctrl> + ; 
(ie press and hold the Ctrl key and then press the semi colon character)

To enter the current time (as set on your PC)

Ctrl key plus colon

<Ctrl> + <Shift> + ; 
(ie Press and hold Ctrl key, press and hold Shift key and then press the semi colon character)

You can also add both the current date and current time into the same cell by using both the above techniques but you must put a space between the two. You can do it in either order (date then time or time then date) it doesn't matter although you will find that, by default, Excel will display the date before the time. This is a valid date/time format and can be used in calculations.

Another method of inserting the current date and time is to use the following function;

=NOW()

Care must be taken if using the =NOW() function as it is dynamic. This means that, every time the Excel worksheet refreshes or performs a calculation, this date and time will also be updated. The short-cut key methods mentioned above are static and will not change unless manually edited.

Introduction to using IF in Excel

All too often the spreadsheet application Excel is used to produce professional looking tables of information. All very good, but, professional looking tables can also be created in Word - something Word was intended as well as other word processing tasks.  The real power of Excel lies in its ability to carry out mathematical operations from the most basic of adding two numbers together to performing complex statistical operations.

Excel has a number (well over 200) built in functions for the user.  An example of a simple function is;

=MAX(A1:A10)

What this function does is really quite logical.  It looks at all the values in range of cells A1 to A10 (10 cells in all) and displays the highest value.

The IF function is also a simple but very useful function, however, how it is used is often confusing to the new Excel user.  In a nutshell what it does is to perform a test and, if that test is true do something but, if the test is false, do something else.  The IF function is constructed as follows;

=IF(Logical test, What to do if test is true, What to do if test is false)

So, inside the (  ) there are three parts separated by commas,  The first part is the test to perform, the second part is what Excel should if the test is true and the final third part is what Excel should do if the test is false.

A simple IF function could look like this;

=IF(A1>=10,"More than or equal to ten","Less than ten")

In the above example the 'test' is to see if the value in cell A1 is greater than or equal to 10 (> means greater than) and, if this test is true (for example if the the value in A1=12) then display the text "More than or equal to ten".  If the value in A1 is less than 10, the text "Less than ten" will be displayed.

Of course, the 'what to do' bits could be calculations like this;

=IF(A1>=10,A1*1.5,A1*2)

Of course, this is just the start.  You can 'nest' functions (ie put functions inside other functions) to perform even more complex tasks.  For example;

=IF(MAX(A1:A5)>=10,"Maximum value is equal to or more then 10","")

In the above example, Excel looks at the values in the range of cells A1 to A5 and if the maximum value in this range is greater than or equal to 10, it displays the text "Maximum value is equal to or more then 10".  If all values are less than 10, then display nothing (hence the empty "") in the third part of the IF function.

Tips for making selections in Word and Excel

When you are using Microsoft Word to create a document, you often wish to select some text in preparation for making some changes to its formatting (ie making it bold or underlined or italic or even a combination of formats).  So many times during my training career, I have seen Word users using the 'drag over' method to select text or even a single word.  In other words, they put the insertion point (the 'I' icon) before (or after) the word, hold down the left mouse button and then move the 'I' icon across the word to select it.  It works, but it is a bit tedious.  Here are a few tips for you to try;

Let's use the saying "The fat cat sat on the mat" as some example text

1. To select the single word "cat"- perform a double click anywhere in the word.
   
2. To select the sequential words "fat cat sat" - double click anywhere on the word "fat" (or "sat"), hold down the <Shift> key on your keyboard then double click on the word "sat" (or "fat").
   
3. To select the non-sequential words "fat", "sat" and "mat" - double click on "fat", hold down the <Ctrl> key on your keyboard, then double click on "sat" then "mat" with the <Ctrl> still held down.
   
4. To select all the text in a document - hold down the <Ctrl> and press the letter "A"

The above techniques also apply to selecting cells in an Excel spreadsheet.

Other selecting techniques you may wish to try in Word are available if you move the cursor to the left of the text until the cursor changes to the following;

1. A single click will select the entire line of text.
   
2. A double click will select an entire paragraph of text.
   
3. A triple click will select all the text in the document.

I find that these methods, for me personally, are the most used.

Format Painter

There are many common features between the Microsoft Office suite applications.  Some we use on a frequent basis such as bold, italic, underline etc.  One feature that often gets overlooked but can save you time is the Format Painter.  In a Microsoft Office 2010 application, the Format Painter can be seen in the Clipboard group under the Home tab;

So, what is the purpose of the Format Painter?  The Format Painter is used to copy formatting applied to text in Word, a cell in Excel, a control on an Access form etc. and apply it to another piece of text, or a cell and so on. For example, you are creating a Word document and you format a title to be bold, font size 20, centered, underlined and in italics.  You wish to apply this same formatting to another title elsewhere in the document so, to save you making all those separate selections to achieve this, use the Format Painter.  Simple 'click' anywhere in the text you wish to copy the format from and click the Format Painter 'button'. This will change the Format Painter 'button' appearance to the following to indicate that it is on;

The cursor will also indicate that Format Painter is active by changing to this;

Now simply click on the next title (piece of text, cell etc.) to apply the copied formatting to this new destination. The Format Painter button and cursor will now revert back to the 'inactive' state.

Tips:  

If you wish to apply the same formatting to a number of different locations (ie several titles, several cells) instead of single clicking the Format Painter when activating it, use a double click.  This will keep the Format Painter active so it will continue to be able to apply the copied format until you perform a single click on the Format Painter to de-activate it.

In Excel, the Format Painter will also copy number formats, conditional formats etc.

Excel Dates and Times - 1

Over the many years I have been using and training people to use Microsoft Excel, one area stands out and seems to cause confusion - that is using dates and times.

Key to understanding how Excel uses dates and times is to appreciate that it represents a date by a serial number and a time as a fraction of a day (expressed as a decimal fraction).  For example, the date 12/8/1998 would have a serial number of 36019.  3pm for the 12/8/1998 would have a serial number of 36019.625 and 7am for the 12/8/1998 would have the serial number 36019.2916666667 (0.625 being the fraction of the day gone by 3pm etc.)  Excel provides methods of displaying these date serial numbers in formats understandable to humans.

Excel is clever.  It recognises when you enter a date in a compatible format (ie 12/7/98).  Depending on the system settings in your particular installation of Excel, you will see on screen something like 12/07/1998 or 12 July 1998 but, behind the scenes, this date has been stored as a number (in this case 35988).

By now you might be wondering why 35988?  Where has this number come from?  By default, Excel allows you to start using dates from 1 January 1900  (ie serial number for 1/1/1900 = 1, 3/1/1900 = 3 and so on).  You cannot perform calculations using dates before 1 January 1900.  There is also an upper limit for dates too - 31 December 9999 (MostExcel users are not inhibited by this upper limit!).

As you look at options available with Excel 2010, you may see the option to use the 1904 date system.  What is all this about?  This allows compatibility with some early Macintosh systems which could not support dates prior to 1 Jan 1904 (all to do with the year 1900 not being a leap year) so serial numbers for these systems started in 1904 and not 1900.

More to come about dates and times in Excel to follow in a future post.

Excel 2010 Tip - Splitting Names From One Column To Two Columns

If you have a list of names comprising of a forename and a surname and they are all in one column, you will know that sorting alphabetically on that column would sort by the forename. ie;

Andrew Johnson

Brian Brown

Carol Anderson

It is more likely that you would wish to sort this list in terms of the surname.  ie;

Carol Anderson

Brian Brown

Andrew Johnson

The easy way to do this would be separate the forename and surname into two columns (As a Microsoft Access database developer, I know that it is always a good idea to to separate names into different fields and, likewise in Excel, it is a good idea to put names into different columns).  The advantage of doing this is that you can then perform a sort firstly by surname and then by forename.  All very logical but, if you are faced with one column in Excel which contains both the surname and the forename, how can you easily turn them into two columns without a lot of tedious typing?

There are (probably) a number of different methods which could be employed.  My favourite is to make use of some of Excel's built-in text functions namely;

• Left(Str$,n) - Return a number of characters starting from the left
• Right(Str$,n) - Return a number of characters starting from the right
• Len(Str$) - Return the number of characters in a string (includes spaces)
• Find(Char$,Str$,n) - Finds the position of a specified character, Char$, from Str$ starting at the position n

In a nutshell, these text functions will do the following assuming Str$ = "Carol Anderson";

Left(Str$,3)  - returns "Car"

Right(Str$,3) - returns "son"

Len(Str$) - returns 14 (A 'space' is counted as a character)

Find("A",Str$,1) - returns 7 (Although there is an 'a' before position 7, it is not a uppercase 'A')

You can use the above functions to separate your list of firstnames and surnames into two columns using the following (Assuming the name to be split is in column A, row 1 and you wish to put the firstname in column B and the surname in column C);

In column B (firstname) enter the function;

• =LEFT(A1,FIND(" ",A1,1))

In column C (surname) enter the function;

• =RIGHT(A1,LEN(A1)-FIND(" ",A1,1))

I shall leave you to sort out exactly how it works!

OK - there are limitations here (beware of extra spaces etc) but you might just find it useful.

Excel 2010 - More than you think

Over the last week I have been delivering Excel 2010 Intermediate training for a local training company based near Telford.  Although I have been using Excel 2010 since it was introduced, it always amazes me that, every time I deliver some training, I find more and more useful features, short-cuts etc. that are new to me.  It is very easy to think that you know a particular software application really well and that there is little else for you to learn - I would beg to differ.  You may be happy using a software application such as Excel 2010 in a particular way but, the chances are, there may be a much more efficient way of performing the same task.  Remember the old adage "You don't know what you don't know".

Training is rarely a 'waste of time' or 'not worth the money'.  Admittedly not all you are told on a training course will 'stick' but my experience shows that you will remember that an application can perform a particular task even if you can't remember the exact process.  Unfortunately, in this time of economic uncertainty or doubt, the training budget is usually one of the first casualties in any cut backs.  This is a shame as this really is a false economy.