Download Excel Examples - Working with dates in Excel

This is the first of a series of blog posts where we show you how to perform some common tasks in Excel - and spreadsheets in general - by providing you with downloadable examples of formulas.

The downloadable Excel example included in this blog post covers working with dates in Excel and will show you: 

  • the Excel formula to show today's date
  • the Excel formula to show yesterday's date
  • the Excel formula to show the date ten days from today
  • the Excel formula to show the day of the week that relates to tomorrow's date
  • the Excel formula to show today's date, in dd/mm/yy format, 'As at' prefix
  • the Excel formula to show today's date, dd mmm yyyy format, 'As at' prefix
  • the Excel formula to show the number of days between two dates
  • the Excel formula to show the number of weekdays between two dates
  • the Excel formula to show the first day of this month
  • the Excel formula to show the last day of this month

The fomulas provided can, or course, be edited to better suit your own purposes. But they work as is and will help you to understand how to work with dates in Excel.

Why I like to put space in Excel formula

I like to put spaces in the Excel formula that I write and I am often asked why.

The short answer is that it makes them easier to read and understand.

The longer answer is that it's one of those things that I've learnt from being a programmer and have chosen to implement in my Excel formulae. Specifically, programmers are encouraged to use white spaces in order to increase readability.

For starters, packing line after line of code is fatiguing and difficult to read. Next, grouping some lines of code together while breaking other bits out with white space - or blank lines - can help to show related areas of code. Related to that, indentation - the use of tabs - is also used in order to show related lines of code.

Given the limited area available to implement white space in Excel formula - it's just the formula bar area - how can space be used to increase the readability of Excel formula?

First a quick caveat: the Excel formula bar does facilitate entering formula over multiple lines and we'll address that another day because it is a great option for very long formula. But today we'll focus on single line formula.

I've previously shown a formula that allows the joining of text and a date and it is shown below.

=A1&" "&TEXT(A2,"dd/mm/yy")

This formula is made up of three parts but it can be difficult to see these three sections and understand what the formula designed to do. So let's put some spaces in there to clarify that.

= A1 & " " & TEXT(A2,"dd/mm/yy")

That's better. Now we can clearly see that there is the text bit (A1) a static spacer (" ") and the date converted to text (TEXT( ... )).

We can probably go one better. The TEXT function takes two arguments - a value (in this case a value that represents a date) and a text format (which in this case is 'dd/mm/yy'). So why don't we show that with a space as well.

= A1 & " " & TEXT(A2, "dd/mm/yy")

That's quite readable now. What do you think?

Remember that when you are writing your formula it is easy to understand what the formula is trying to do. But six months down the track this understanding may not come so naturally to you. Or it might not even be you trying to read and understand the formula - it might be someone else. Do yourself a favour and put some space in your Excel formula to help your future self. Or that future other person.

How to use Excel to join cells and the difficulty of joining dates

A common thing to do in Excel is to join the contents of two (or more) cells. 

Assuming cell A1 contains the word 'Joe' and cell A2 contains the word 'Bloggs' than in cell A3 you could join the two cells using the formula ...

= A1 & A2

... and A3 will show

JoeBloggs

This isn't ideal. So let's add a space in between and change the formula in cell A3 ...

= A1 & " " & A2

... and now A3 will show

Joe Bloggs

That's better. But a funny thing happens when you try to join a string and a date. So now let's assume that cell A1 contains 'As at' and A2 contains '18/04/2016'. If cell A3 still has the formula ...

= A1 & " " & A

... then cell A3 will show ...

As at 42478

That's not what we wanted. This happens because Excel represents dates Excel stores dates as a number that represents the number of days since the beginning of the year 1900. In our example, there have been 42,478 days since the start of 1900.

We can fix this by wrapping the reference to the cell that has the date in it (i.e. A2) in Excel's TEXT function. So now if A3 is changed to be ...

= A1 & " " & TEXT(A2, "dd/mm/yy")

... then A3 will show ...

As at 18/04/16

That's better. We can change the second part of the TEXT function to format the date differently. For example: 

=A1 & " " & TEXT(A2,"d mmm yy") will show As at 18 Apr 16

=A1 & " " & TEXT(A2,"ddd dd mmm yyyy") will show As at Mon 18 Apr 2016

=A1 & " " & TEXT(A2,"dddd dd mmmm yyyy") will show As at Monday 18 April 2016

The are loads of options to format the text of dates but hopefully this is enough to get you started.