Tag Archives: formula

Excel Keyboard Shortcuts – Function Keys

More Excel keyboard shortcuts – the Function keys

F1 – Excel Help

F2 – Edit active cell

F3 – Brings up the Paste Name box – allowing you to easily refer to any named ranges

F4 – Repeats the last action (or in a formula cycles through absolute reference options)

F5 – Go To

F6 – Flicks through the worksheet, ribbon, task pane, and zoom controls

F7 – Spell checker

F8 – Toggle Extended Selection – keeps highlighting cells until you switch it off

F9 – Calculate now (on active worksheet)

F10 – Just like pressing Alt

F11 – Create chart – uses range around the active cell

F12 – Save As

XL FORMULA FOCUS: Need to calculate length of service or age? Use YEARFRAC

Length of service is used all the time in HR – for example long service awards or calculating redundancy payments. Those of you fortunate enough to have a solid HR system in place will hopefully have some automation to keep you aware of upcoming key dates, but for others Excel can be the next best thing.

We will be using the YEARFRAC function:

YEARFRAC(start_date, end_date, [basis])

 

Let’s do an example…

1. In cell A1, enter your date of birth (for arguments sake use the format dd/mm/yyyy).

2. In cell B1, enter  =TODAY()  . This will return today’s date, which will update automatically as time goes on.

3. In cell C1, enter  =YEARFRAC(A1,B1,1)  .

 

The number one at the end of the formula is what is referred to above as the ‘basis’. It tells Excel on what basis to calculate the difference between start_date and end_date. Personally I’ve always used 1, which is described as Actual, and it has always been accurate and fit for purpose. If you’re unsure, while working on the YEARFRAC formula hit the fx button next to the formula bar, and then select ‘help on this function’. It will give you a bit more information about the YEARFRAC function and detail the various basis options.

 

So, cell C1 should now show you the number of years between your start date and end date (in this case how old you are today). Depending on how you format this cell (and assuming its not your birthday – if it is happy birthday!) you will probably have a number of decimal places shown. This is fine if you want a very precise figure, but in many practical applications you will want completed years (e.g. I am 30 years old, not 30.153663 years old). So for this we’re going to use the INT function (short for Integer).

 

4. In cell D1, enter INT(C1)  .

 

You will now have your age shown as an integer.

If you wanted to you could condense this into fewer cells.

 

5. Select cells B1, C1 and D1 and press delete.

6. In cell B1, enter this:

=INT(YEARFRAC(A1,TODAY(),1))

You can now use this formula repeatedly, changing the contents of cell A1 to any date that you want.

If you’ve made it this far Well Done! You’ve used 3 different functions in one formula to give a practical output.

If you liked that, here’s a post about EDATE, a really straightforward date function that I use to calculate the of probation periods or notice periods.

Learn Any Area of Excel using these 80 Links

Here’s a fantastic post from Chandoo, a prolific Excel blogger that I follow: http://chandoo.org/wp/2011/12/12/learn-excel-by-topic/

The post contains links to 80 different lessons across a wide spectrum of Excel functionality.

Check it out and see what you can learn.

If there’s an Excel topic you want to know more about just tweet @xlcalibre, e-mail admin@xlcalibre.com.