Serial Numbers

Our blog Microsoft Excel – Understanding Dates looked at how the program stores dates behind the scenes.  We found that the date 30/06/2016 (which just happens to be my birthday) is actually stored as the number 42551.

This Serial Number turned out to be the number of days since 1st January 1900.  Serial Numbers make it easy to do arithmetic with dates.  Want to know what the date will be in thirty days time?  Just add 30 to today’s date and Excel will work it out for you.

This time we are going to look at some the Functions Excel provides which work with dates.  We will also explore some tricks to change the way a date looks.

Date Functions

Suppose you need a list of dates, one month apart, but always on the same day (in this example the 21st).  Simply adding 30 to a date isn’t going to work, because not every month has thirty days:

Three of the Functions in Excel can take a Serial Number, which represents a date, and split it into its constituent parts.

  • =YEAR – returns the Year portion of a date as a simple integer
  • =MONTH – returns the Month portion of a date as a number between 1 and 12
  • =DAY – returns the Day portion of a date as a number between 1 and 31

 

The =DATE Function does the opposite.  Provide it with the three numbers which make up the constituent parts of a date; it creates the corresponding Serial Number.  [For some reason you have to give Year, Month and Day in that order]

So to get the same day in every month, use a formula like this:

  • =DATE(D3,E3+1,F3)

 

When I first discovered this technique I thought “This trick won’t work at the end of the year”.  I realised using =MONTH on a date in December will give 12.  This would mean that the formula for the next month would resolve to =DATE(2016,13,21).

Surely that would give an error.  Bill Gates and his chums thought of that one – it just works!

 

Date Formats

If you want to change the way a date looks in Microsoft Excel, the [Home] tab of the ribbon gives you two simple choices:

  • Short Date will format our date to look like – 21/06/2016
  • Long Date displays it as – 21 June 2016

 

When I first started using the program, these easy options weren’t available.  You had to know the correct Formatting Codes to get the effect that you wanted.

Those codes are still around today and they give you great flexibility when formatting dates (or any other type of numeric entry for that matter).

  • Choose [Home / Cells] – Format, Format Cells… then click on the Custom category to see this dialog box:

 

The entry highlighted – dd/mm/yyyy – is equivalent to the Short Date format shown earlier.  However, you can create your own Custom code from the building blocks available.

Here are some examples:

  • dd — displays the day as two digits – e.g. 21
  • ddd — displays the day as a three character text abbreviation – e.g.Tue
  • dddd — displays the day as text in full – e.g. Tuesday

Similar concepts can be applied to the Month portion of a date:

  • mm — displays the month as two digits – e.g. 06
  • mmm — displays the month as a three character text abbreviation – e.g. Jun
  • mmmm — displays the month as text in full – e.g. June

The Year portion can be formatted with these codes:

  • yy — displays the day as two digits – e.g. 16
  • yyyy — displays the day as four digits – e.g. 2016

The following illustration shows these codes in action.  Bear in mind that, in each example, all I have done is entered a date.  It’s the Formatting Codes that change the way they look.

 

Remember our list of dates which are one month apart?  Supposing we need to know which day of the week that the 21st falls on each month.

That is now easy.  Below, I’ve inserted an extra column (C), added a simple formula (cell C3 contains =B3 and so on), and then formatted the dates in the new column with the following code: – (dddd)

 

There’s More!

We have only really begun to scratch the surface of what Formatting Codes can do for you in Microsoft Excel.  I’ve not said anything about working with Time entries.  Don’t get me started on Four Part Custom Formats for numbers!

If there is anything I can do to help you, get in touch via info@base2.co.uk