We often need to
work with dates in Excel.
Sometimes we want to
record the date that something happened - we made a purchase, someone purchased
from us, we issued an invoice, etc. Other
times we want to calculate using dates to work out for example, how many days
since an event took place or what the date will be 28 days from now.
There are lots of
things that Excel can do with dates but first of all it is important to
understand how Excel works with dates.
When you see a date
displayed in a cell, you are seeing a number with a date format applied to
it. Starting from 1st January 1900, each
day is one whole number. I am writing this
blog on the 5th August 2013 which is day number 41491. Specific times of day are
expressed as decimals e.g. 12 noon on 5th August 2013 is 41491.5. This means that dates can be added and subtracted
and used in formulas just like regular numbers, because as far as Excel is
concerned they are just regular numbers.
Of course Excel is
more clever than that. There are several
built-in functions that allow you to work with dates. Take a look on the Formulas tab in the Date
& Time button to see a full list.
You can extract just the month or the year from a date, you can find the
day of the week for any date, you can calculate the number of workdays (i.e.
excluding weekends) between two dates or find the date that is a specific
number of workdays from a given start date.
There are many more functions like these.
Excel also
recognises dates as series which means that you can use the Autofill handle to
complete a series of dates without having to type them all in. The series does not have to be sequential
days. Try the following:
Enter
today's date in a cell (Ctrl + ; is the quickest way to do this)
Click
on the Autofill handle (the small square on the bottom right hand corner of the
cell) and drag it over several cells to the right.
You
should find that Excel fills in sequential days.
You
will see options to fill days, fill weekdays, fill months or fill years
Click
on Fill months. You'll see that the
dates filled in now are the same day of sequential months
You
can also fill in a custom interval between dates.
In
a new row type in a date e.g. 01/01/2013.
In the next cell type in a date two weeks later e.g. 15/01/2013
Select
both cells then drag the Autofill handle and Excel will continue to fill dates
that are 14 days apart.
Now that you
understand how Excel calculates dates you're ready for my next blog post where
we will take a closer look at some of Excel's date functions.