There are several functions relating to dates and times that can be used in Excel, which are particularly useful for bookkeepers and accountants. Imagine having a column next to your invoice dates which calculates when that invoice is due? You may have different payment terms with different suppliers and customers, so it might not just be a case of adding a month – and who can keep track mentally with all the paperwork flying in and out of a business every week anyway?!
Important 1: US readers, remember we work in dd/mm/yy in the majority of the world. I apologise if your date system means you get addled by looking at the dates I have here!
Important 2: Mac users, there is a critical date issue you need to be aware of. See below for more info.
How do dates work in Excel?
Behind the scenes, every date is stored as what’s known as a ‘serial number’. That means each day has a given number. The following day is the next number; the previous day is the previous number.
This is why dates and times align to the right of a cell, just like all numerical data. (Click here for a reminder – or here for more ways to format dates.)
What is ‘Day 1‘? Enter the number 1 in a spreadsheet, and format the cell to Date. Can you see it’s the first day of January, 1900? That’s our very first day, when Excel thinks it was born!
Now enter today’s date in another cell. Format the cell to General and you’ll see the serial number behind it. For instance, right now (30th March, 2020) we’re on day 43920.
All this is kind of theoretical, but it does help to know what’s going on when you import some data with dates and you get weird looking 5-digit numbers instead of actual dates. Also, the fact that behind the scenes they’re numbers means you can perform calculations off them.
Entering today’s date into Excel – three ways
You may not necessarily need to know (1) for the exam, but you will definitely need (2) and (3).
I put all the examples below into a spreadsheet which you can download and play with from here.
1) A quick way – doesn’t update
You can dump today’s date into your spreadsheet in lightning quick time with a nifty keyboard shortcut. Press Ctrl and tap the ; (semi-colon) key. Ta-da! Note: don’t press and hold the semi-colon key or you’ll get loads of dates, one after another!
There’s a disadvantage with this method – and that’s if you open the spreadsheet tomorrow, it’ll still have today’s date.
2) Using the Today function – DOES update
This is one of a handful of functions with no arguments. Literally – no arguments at all. Curious – but that’s the way it rolls.
Find a blank cell. Type =today( ) and confirm. You’ll see today’s date in your cell. And if you were to open your spreadsheet tomorrow, you’ll see tomorrow’s date. Note that’s literally nothing between the brackets – open and close with no arguments.
3) Using the Now function – does update
This is also a function with no arguments, but it behaves slightly differently from TODAY.
Find a blank cell (make sure it’s a different one from the one you used in (2) just now). Type =now( ) and confirm. Again, you’ll see today’s date – but with the time as well.
The key difference is therefore that TODAY just has the date but NOW has the date and time.
And because it has the time, we can see it in action. Make sure you’re in the cell with your NOW, and press the F9 key on your keyboard – can you see your time has updated? If not, wait a couple of minutes and try again. F9 always ‘refreshes’ a formula (that is, recalculates it) so it’ll boost your NOW to return the latest time.
Important note for Mac users – or for anyone who shares spreadsheets with Mac users
Microsoft, when it was first competing on the market, made its spreadsheet functionality completely compatible with Lotus123 – a spreadsheet package that is now pretty much obsolete – so that it could entice Lotus users to migrate to Microsoft. I mentioned above that ‘Day 1’ is 01/01/1900. What Lotus123 didn’t realise – and, hence, Microsoft Excel – is that 1900 was not a leap year. That’s why you can actually have 29/02/1900 and it will work. (Try it: type 29/02/2018 and it will left-align; Excel won’t recognise it as a valid date and will treat it as text; type 29/02/1900 and it will right-align as it’s a recognised date.)
The upshot of this is that dates are out by a single day. BUT, when Microsoft came to release their first version of Excel for use on a Mac, they set ‘Day 1’ to be 01/01/1904 (which is a leap year)… which means that Mac dates may be 4 years and 1 day ahead of PC dates.
The good news is you can switch between the two. You’ll need to click on File, Options, then Advanced. Scroll way down – in the section When calculating this workbook you’ll see Use 1904 date system. Place the tick in there, and you’ll see all your dates advance by 4 years and 1 day.
Other date and time functions
As far as I can make out, the exam only requires you to have a working knowledge of TODAY and NOW. However, there are several other date functions which are dead handy to know about, so I’ll review them here. If you don’t care for this (and when it comes to working with time in Excel, I don’t blame you!) then please feel free to click here to go onto the next topic.
The DATE function
This is useful if you have your days, months and years in separate cells, rather than written all in a single cell. This may happen if you import data from non-Excel sources.
Typically, when we join data in separate cells together, we would use a function like CONCATENATE or TEXTJOIN, but these are text functions and won’t render your date in a format that allows you to perform calculations or apply useful formatting. If you try a text function, you’ll note your date is aligned to the left of the cell – a dead giveaway that it’s been interpreted as text data, not as numerical data.
The syntax for DATE is really easy. It has three arguments: year, month, day. So as you can see from the image below, as our year is in A3 that’s our first argument, our month is in B3 so that’s our second argument, and our day is in C3, so that’s our third and final argument.
When you first put it together you may get a serial date as a return – don’t worry, just format it as a date.
Can you note something funny going on in cell B7? What’s going on there – we can’t have 17 months! Well, when there are more months than 12 (or more days than there should be for the given month), Excel adds the excess on. So in this example, it’ll take the 1st January 2017 and add 17 months onto that – which is firstly 12 months (1st January 2018) then an additional 5 months (which takes us to 1st May 2018).
Adding a number of days, months or years to the DATE Function
It’s fairly straightforward – simply add the number to the relevant argument: =DATE ( A3 +5 , B3 , C3 ) will add five years to the year in cell A3.
What if my date is in a single cell, rather than split out across three?
Good question – in this case of course it’s a serial date and our DATE function will behave a little differently.
Let’s assume our serial date is in cell A3. You would lay out your DATE like this:
= DATE ( YEAR (A3) , MONTH (A3) , DAY (A3) )
Each time we’re having to refer back to A3 for all three components of the date.
Adding, say, five years to this, it would be laid out thus:
= DATE ( YEAR (A3) +5, MONTH (A3) , DAY (A3) )
Calculating 30 days + 7 days for VAT returns
Your VAT is due one calendar month after the end of the quarter, with an additional 7 days added if you pay electronically. Let’s say our quarter ended on 30th April. Adding one calendar month gives us 31st May, and the additional 7 days gives us 7th June. This can be a bit fiddly to calculate, given that not all months have an equal number of days.
Start off by placing your quarter-end date in a cell, let’s say A1.
In the next cell, we shall use the End-of-month function, which is EOMONTH. EOMONTH has two arguments: the first is the source cell (in this case, where we typed our quarter-end date – A1). The second argument is a referer to how many months forward we want to run: 0 (zero) means the end of the current month; 1 means the end of the following month, and so on. (In fact, you can use negative numbers to count backwards by months, should you need.)
So effectively, our function will be =EOMONTH (A1, 1) because we want the end-of-month date for the following month.
Now we need to add the 7 days on. Edit your function and write +7 on the end:
=EOMONTH (A1, 1) +7
Corporation tax and Self-Assessment returns
We can use this to work out when we need to file and pay other taxes, too. The rule with Corporation Tax is payment by 9 months and submission by 12 months after the period end. Self-Assessment is a little easier as it’s just the 31st January following the end of the period to 5th April the previous year. So if you set your period end to be 5th April, you can use EOMONTH to count forward and give us the last day of the 9th month ahead.
Using this in practice
I have at work a list of clients with their period ends. In the following two columns I have formulae to calculate when tax is due for payment and when returns are due for filing. I then have a conditional format which highlights any client whose submission is due within 2 months – so I have a forward reminder of who to focus on. My conditional format uses the TODAY function, so it always updates.
Most people can remember the big annual self-assessment deadline but it might get a bit more fun when Making Tax Digital (MTD) rolls out beyond VAT. There have been suggestions that SA will become quarterly, in which case – unless HMRC sets the periods themselves (as it does at present) – it might be you really need to keep on top of when your clients’ returns are due. Having something that reminds you will become vital, so using EOMONTH may well become very familiar.
Calculating working days
Using the VAT example above, we have one calendar month + 7 days to file, plus, if paying by Direct Debit, an additional 3 working days before payment is collected. There are many other reasons why you might want to calculate working days between two dates, and there are two main ways to do it.
Networkdays
This function is very overlooked. Quite simply, you plug in a start date, an end date, and (if relevant) the number of holidays (non-working days) falling within that period, and it will tell you how many working days have elapsed.
As you can see from the image below, I have my bank holidays listed in cells F1:F8, which is the final argument of the NETWORKDAYS in cell B5. This has deducted three additional working days between 01/01/2018 and 04/04/2018.
The date in B4 is a TODAY, so the result in B5 will keep updating every time I open this spreadsheet.
Of course, my list of holidays could include staff leave as well, if I needed to work out how many days a team was spending on a project.
WORKDAY
The WORKDAY function is similar, but is based on the premise that you don’t know your end date (as you do with NETWORKDAYS), just how many days you need to work on a project (for example).
So you can see how similar this is to NETWORKDAYS – plug in a start date and a number of days on the project and you get out a date. Incidentally, in the example above, the result of my WORKDAY in cell B5 was formatted as General, so I had to go back and format it as a date.
So now you’re thinking, “OK, I’d like to have a formula that calculates that additional 3 days on the VAT submission date, how do I do that?” You’re going to think I’m thoroughly mean here as I won’t tell you… because I haven’t yet worked it out myself! I’ll ponder it, and if I nail it I shall update this post. Have a go yourself, though – remember, any kind of practice you can give yourself prior to the exam is going to stand you in excellent stead!
Calculating the difference between two dates
This is commonly used to work out how old someone is, or how long someone has been working somewhere – so it could equally apply to how old your payables are. There are two ways to do it:
1) Subtraction
As your start and end dates are both numerical, it’s possible to take one away from the other. Yes, as simple as that. However, do be warned that this might not give you the answer you expect.
Say I want to know how many days between 01-Apr and 05-Apr. If I said 05-Apr minus 01-Apr I will get the answer 4. Is that correct? Or do I need it to say 5? If so, I should say 05-Apr minus 01-Apr plus 1. The two images here should show you the difference clearly enough.
2) Using the DATEDIF function
This is a curious function, because it’s been left out of the Help files of most versions of Excel for at least 20 years. (From what I gather, it was originally in Lotus123 so I’m guessing it’s a ‘legacy’ function.)
History lesson aside, the syntax can be quite hard to get your head around – or at least, the final argument. Here’s the syntax:
DATEDIF ( start_date, end_date, unit )
The final argument, unit, relates to what kind of output you want – years, months or days. This is why it’s handy (I’d say essential) in calculating ages, as using other formulae or functions gives an output in days.
If we want to say how many years have elapsed, we need to say
DATEDIF ( start_date, end_date, “y” )
If we want to say how many months have elapsed, we need to say
DATEDIF ( start_date, end_date, “m” )
If we want to say how many days have elapsed, we need to say
DATEDIF ( start_date, end_date, “d” )
(Knowing how many months something is may seem a bit useless; I never came across a need for this until I started studying Level 4. I’ve discovered that how many months something happened for is necessary for certain tax calculations – particularly, what I’m thinking of is calculating PRR in a CGT calculation on the sale of a property.)
So in order to make DATEDIF more useful, we can put this together to have a formula which will return something like “18 years, 5 months and 6 days”. This will involve several steps.
To tell DATEDIF to ignore whole years and just give us the months left over:
DATEDIF ( start_date, end_date, “ym” )
On a similar basis, to ignore all the months and just give us the days left over:
DATEDIF ( start_date, end_date, “md” )
Putting a DATEDIF together
We want to say 18 years, 5 months and 6 days. The red text is our DATEDIF (we’ll need three), our green text is added so people know what they’re looking at, and we’re going to join every stage together by using a &. Here goes… it gets tricky so be careful!
A little tip: a lot of people forget to put spaces into their additional text. It’s hard to do this at first when you’re a learner, so I suggest doing everything without (in which case it’ll look like 18years,5monthsand6days) then going back and putting them in!
(Note – I know my double-quote marks are formatting badly, I apologise for that.)
To get the years: =DATEDIF ( start_date, end_date, “y” )
Then join this to the next bit: &
Then insert ” years, ”
Then join this to the next bit: &
To get the months: DATEDIF ( start_date, end_date, “ym” )
Then join this to the next bit: &
Then insert ” months, and ”
Then join this to the next bit: &
To get the days: DATEDIF ( start_date, end_date, “md” )
Then join this to the next bit: &
Then insert “days”
Put together, that looks like:
=DATEDIF(B1,B2,”y”) & ” years, “ & DATEDIF(B1,B2,”ym”) & ” months, and “ & DATEDIF(B1,B2,”md”) & ” days”
You can see what I mean that you have to be patient and careful! I usually forget a bracket or an ampersand & and have to go back and edit.
While this is a fun function to plug in your birthday and TODAY( ) and find out how many years, months and days old you are, I’m guessing you could also use it to work out a retirement date or how long someone has been on maternity or paternity leave (I don’t work in Payroll so I’m not sure about this, but I’m sure you get the idea).
Time formulae
Goodness me, I absolutely detest working with time in Excel. It’s down to the way my brain processes time, which is sturdily fixed on 60 seconds to a minute, 60 minutes to an hour, 24 hours to a day.
The way Excel processes time, on the other hand, is decimally. What I mean by this is that it takes a day and divides it by 10, 100, 1000. To understand this, go to a blank cell and type in 1.5. Now, format it as Time. Your result is 12:00:00! Have a look in the formula bar – you’ll see that it’s actually 01/01/1900 12:00:00. Well, halfway through the day is indeed midday – so of course it would end up showing 12:00:00.
So, have a think for a minute (ha ha) – what would you enter in order to have a time of 9am? 8pm? 4:35 in the afternoon? Fortunately, you don’t have to puzzle too much, because all you need to do is actually type those times in (in 24 hour clock format) and Excel will format this appropriately. Change the format back to General and you will see what it is in terms of a fraction of a day.
(If you’re curious, 4:35 pm is 0.6909722 of a day.)
If you type a time in as 0.xx then Excel will treat it as time and time alone. If you type in a time as 1.xx or 43912.xx it will take the whole number portion as a date.
Using time in spreadsheets
The biggest use of times in spreadsheets is when creating a timesheet, where staff can enter their start and end times and it will calculate how long they worked.
You need to be hyper-aware of one crucial thing if you design a timesheet: users may input time as a decimal, such that a start time of 8:30 will be input as 8.30. This will actually be read by Excel as 08/01/1900 07:12:00. Alternatively, they may read 8.5 as eight hours and fifty (or even five) minutes.
So you need to make absolutely sure you use your Data Validation settings and let users know that time must be input with a : colon.
Calculations with Time
Some older versions of Excel had an additional problem with spreadsheets tracking time. Just as with dates, to calculate how long a person worked, you take the start time away from the end time (e.g. 19:00-11:30) and that should give you how long a person worked. But what if they are shift workers? Imagine clocking on at 8pm and working through till 8am… that would give a formula of 08:00-20:00, which is impossible. In order to fix this, having the date as part of the time will help, but of course your users may find this a tremendous faff.
Secondly, when adding up all the hours over a week that a person has worked, most likely they’ll go over 24 hours if they’re a full-time worker. That means that as soon as they go over, the total cell might look like it’s deducted 24 hours altogether. For example, if you work 35 hours, then that’s 11 hours over a full 24 hours – so your total cell might give a result of 11:00 rather than 35:00.
Fortunately, time totals are now recognised as such, and will behave correctly – but you can still get formatting issues. If it does misbehave, the Custom Number Format [h]:mm should sort it.
I had a fun challenge recently, when I was commissioned to create a spreadsheet to track how many hours had been worked daily on a project and totalled for the week. This meant that instead of 9 till 5, it could be 9 till 9:30, then 10:05 to 10:25, then 12:15 to 14:00 and so on. In the end, I had to use ‘helper columns’ to assist – additional columns to do the hard work – which I then placed on another worksheet and hid so they wouldn’t be deleted by accident. (You can find about hiding and protecting worksheets here.) I’ve included this in the example spreadsheet so you can see how I went about it.
That’s it for dates and times! Remember, all you really need for the exam is TODAY() and NOW(). Hopefully you do now have a bit more understanding of the kinds of things you can do with this functionality, though.
If you’re following the suggested Learning Plan, please click here to go to the next post.
Excel for Accounting Students 