Training Archives - Page 2 of 3 - Base2 Training

Category Archives: Training

Microsoft Access – Hide Footer Entries

I’m here to help

James contacted me as part of our Training PLUS service.  This allows people who have attended one of our courses to ask for help with any problems they encounter when trying to put the training into practice. [The Training PLUS service is entirely free!]

In James’s case, he was attempting to create a report in Microsoft Access. He wanted the elements he had put in the Page Footer area to appear only on the first page of the report, and this was proving difficult to achieve.

Why Train the Trainer?

I know what you’re thinking. How can you train someone to deliver a course in a subject you have absolutely no expertise in ?

Therein lies the difference.

Whilst we cannot (and should not) pretend to be specialists in the subject you need to deliver training in, we can show you some fantastic ways of engaging with your attendees whilst delivering focused highly effective training.


Microsoft Excel – Understanding Dates

An Interesting Problem

A while back a client got in touch with me.

Sarah had downloaded a file of financial transactions from her bank and loaded them into a Microsoft Excel workbook. When she looked at the dates against each transaction, something strange had happened. Every date was exactly four years and one day earlier than it should have been. What on earth was going on?

How are Dates Stored?

This turns out to be all to do with the way Excel stores dates behind the scenes.  In the following illustration, I’ve typed the same date twice (which just happens to be my birthday!).  The version on the left is formatted to look like a Date, the one on the right is formatted as a General number:


When you enter (say) 30/06/2016, Excel doesn’t actually store those characters.  Instead, it stores a Serial Number which represents that date.  The Serial Number for 30th June 2016 is 42551.

Where does that number come from?  It’s the number of days since 1st January 1900 – honest!

You can prove that by trying it the other way around.  Type 10 into an Excel worksheet and then format it to look like a date.  You’ll get 10/01/1900.  Entering 10000 will give you 18/05/1927, while 20000 equates to 03/10/1954.

Why is it done that way?

The developers of spreadsheet programs decided to use this system so that arithmetic with dates was easier to organise – [incidentally, this is a concept which pre-dates Microsoft Excel].

If the dates were stored using the characters that you type (i.e. 30/06/2016), a formula to calculate an entry for the following day would be quite complex.   Issues like “thirty days hath September” would have to be taken into account.

When a date is stored as a Serial Number, all you have to do is add 1 to that number.  Microsoft Excel has built-in coding to convert the new number into the correct date:


What has this got to do with Sarah’s problem?

There is a little known option in Microsoft Excel called – Use 1904 date system.  When this is turned on, Serial numbers are calculated starting from 1st January 1904.  [Why have two difference systems? – I’ll explain that later in this blog]

If you type 30/06/2016 into a workbook which is set to use that system, the serial number stored is 41089:


There is no problem in having two systems, as long as you are consistent.  The option affects an entire workbook so copying dates between sheets in the same workbook is fine.

However, try copying a cell containing 30/06/2016 from a workbook using the 1904 system, to a workbook using the 1900 system.  What you are actually copying is the Serial Number 41089.  In the 1900 system this number gets translated to 29/06/2012.

This is exactly four years and one day earlier than it should be – which was precisely Sarah’s problem!

A Bit of History

The earliest versions of Excel for the Mac only had the 1904 date system – (in fact hearing that Sarah was using an Apple Mac computer was my first clue towards solving the problem).  Later versions gave you the option of choosing which system you wanted to use, as do Windows PC versions.

The Mac designers remembered that the year 1900 wasn’t a Leap Year.  The rules say that the first year of a new century is only a Leap Year if it is divisible by 400 – the year 2000 was one, 2100 won’t be.  By staring their Serial Numbers at the 1st January 1904, they could avoid any complications arising from that fact.

Interestingly, Lotus 1-2-3 (the market leading spreadsheet program of the mid-1980’s) allowed you to enter 29/02/1900 as a valid date.  Its developers forgot about the 400 year rule.

This gave Microsoft a dilemma when creating Excel.  On the one hand they wanted the program to be accurate.  On the other hand they wanted the two programs to work the same way – (so that they could lure the maximum number of users to move from Lotus 1-2-3 to Excel).

They decided to deliberately leave the “bug” in place.  If you format an Excel date to show the day of the week, it gets it wrong for dates before 1st March 1900.

How did I get the date 01/03/1900 to display Thursday?  Find out about that (and a few more tricks for manipulating dates) in our blog Microsoft Excel – Working with Dates.

Microsoft OneNote – ever thought of using it?

What is it?

Even in this modern, digital age, I still get a lot of paper-based information through the letterbox. To keep from drowning in it, I do like to be organised (to a degree). I have ring-binders with Bank and Credit Card Statements in them. Others containing letters and offers. There is a folder with handwritten notes in it about the “New Garden Shed” project.  Maybe it is an age thing, but I just feel more comfortable doing it that way.

However, I’m a computer person as well.  I’ve been tracking my Bank Statement in an Excel spreadsheet for years (it does do negative numbers!).  Excel is very structured; its rows and columns layout is well suited to storing financial transactions.

The file of information about the shed isn’t structured at all, so finding things is tricky.  I could type it all into a Word document, but the layout would be too formal.

This is where Microsoft OneNote could come in useful.


Microsoft PowerPoint – Templates and Themes

What is the Difference?

In Microsoft PowerPoint, it can be difficult to understand the relationship between a Template and a Theme.

Template files contain one or more Themes, but you can also save a Theme file separately.  Which is more important and how do they interact?

This is the first of a series of blogs in which we explore the use of these two features to create a personalised style or impose a corporate look to your presentations.

Two definitions may help:

A PowerPoint Template is a blueprint of a slide or group of slides that you save as a file.

A Document Theme is a set of formatting choices that are designed to coordinate well together, and give your presentations a designer-quality look.


Scheduled or Dedicated Training?

When you are deciding which type of training course to go for, should you pick Scheduled or Dedicated?  What is the difference?

Scheduled or Dedicated – Scheduled Courses

These are “Open” courses which run on a regular basis (usually monthly).  Attendees from any organisation are welcome.  There is no minimum number of attendees required, so if you only have one person in need of training at the moment, this could be the perfect solution.


Microsoft Project – Scheduling Tasks

Linking is the key

Microsoft Project is a great entry-level application for Project Management.  You can create a list of Tasks, estimate their Duration’s and link them together with Dependencies.  This is done by analysing which Tasks can’t start until a previous Task has finished.

The program can then calculate the Start and End dates for every Task and therefore give a predicted date for the project’s completion.

What’s more, because the Tasks are linked, when you enter the fact that one of them has over-run, the delay is automatically reflected in the dates of the Tasks that follow (its Successors).

Also, there are a host of other features that enable Microsoft Project to model situations which may occur in the real world.


Microsoft Excel – Data Validation

You are at their mercy!

When you create a spreadsheet for your colleagues to use, they could type in absolutely anything.  Incorrect entries will make the calculations, which you slaved long and hard over, generate the wrong answers.  As the old saying goes – “Garbage In, Garbage Out”.

Whilst you can’t be looking over their shoulders all the time, Microsoft Excel has a nifty feature to help ensure that the data typed into a spreadsheet is at least in the correct “ball-park”.

You can force them to enter a Whole Number or a Decimal, insist that the entry is a Date or a Time along with a variety of other settings. It is even possible to provide a fixed List of choices for them to pick from.


Microsoft Office – Things that make me mad!

It makes me mad!

I don’t know about you, but I get really irritated by those little things that change between the different versions of the programs that I use.  The things that ought to be the same.  The things where there is no logical reason for them to be different.

You get into the habit of performing tasks in a particular way.  So, because you are in that habit, you are working efficiently and quickly. Then you are presented with a new version – and you get tripped up by the inconsistencies.


Microsoft Excel – Absolute References

Different types of References

Ever inherited a Microsoft Excel spreadsheet from someone, looked at the formulas and thought “what are all these dollar signs about?”.

Well, they are Excel’s way of defining what will happen to Cell References if you ever copy that formula to a different location.

In fact, there are three types of references available – Relative ReferencesAbsolute References and Mixed References.


1 2 3