Skills level: Beginner
Have you ever wondered why dates are sometimes displayed as a random number? Usually (these days) it starts with a number four, and would be 5 numbers in total.
It almost seems like dates are coded in some way. If you thought that, you are absolutely correct. Each and every date has a unique whole number assigned to it, called serial numbers. If you look at the image above, you will see that the numbers follow each other. 20 Aug 2016 is shown as number 42602 (if not formatted correctly as a date), the following day is 42603 and so on.
This property allows you to be able to perform calculations with dates in excel. Because a date is equal to a whole number, you can subtract dates from each other. For example, you may want to know how many days there are between the 20th of August and the 3rd of October - maybe your holiday starts then and you want to start counting down the days!
Or you might add a number of days to a certain date to know when the due date of your project is - your boss may have told you to give feedback on your research in 20 days.
(Make sure cell B1 and B3 are formatted as dates, and Cell B2 is formatted as “number” or “general”, otherwise you’ll get a funny result.)
And yes, you can check the days on your desk calendar…. But it is so much more fun playing around with it in Excel!
So where did it start you might think. Excel had to start “counting” at some point… So what date was the number 1 allocated to?
Very easy to figure out, and you may have guessed it yourself. Open a blank spreadsheet, leave the format for column A as is (it usually defaults to “general” in a new worksheet), and change the format for column B to “Date”. Then, enter the number 1 in both cells A1 and B1.
And there is your answer. It all started on 1 January 1900.
This effectively means that today, the 20th of August 2016, is 42 602 days after 1/1/1900. Fun fact that you can share with a fellow Excel geek! :) (Please don’t share with anyone else, they will find it unamusing and it may adversely affect your social popularity).
To be able to use dates in Excel effectively, it is very important to use the correct format when entering dates in your spreadsheet. When entering dates manually, entering it as “20.8.2016” for example (using periods to separate day, month and year), won’t cut it. Excel does not recognise it as a date and will not be able to use it for any calculation or manipulation of any kind. So make sure that you always enter dates in the correct format, so that you can use them in your calculations if need be.
While we are on dates, I am going to jump straight into the TODAY formula, which is very easy to learn and can come in quite handy when you often have to enter the current date on documents.
The function to enter is as follows:
You will notice that it has no further arguments to enter. It simply instructs excel to use the date, as set on your computer, in the particular excel cell. As easy as that.
It is important to note however, that if you use the formula today, and you save your excel document, and you open it again tomorrow, it will show tomorrow’s date. It will not store the date on which you entered the formula the first time, but updates as you continue to work with the document over time.
This is useful where you maybe use an excel invoice template to bill clients. The day that you enter the details, is the same day you print the invoice and send to a client and print a copy for you own file. And you want your template's date to be updated to today's date every time.
Shortcut to enter current date in a cell
And a further useful little trick (it changed my life!) is the shortcut to enter today’s date. Simply hit “Control” and the “Semicolon” keyboard button to enter the current date in any cell on your spreadsheet.
This allows Excel to grab a look at your computer’s date at the time, and use that information to display in your Excel cell.
This is different from the TODAY formula as it simply enters the data, or the serial number for today’s date (and not the today formula). It will therefore not update or change as with the TODAY formula, but will be static or absolute data. Imagine Excel simply taking a picture of the current date and storing it in a cell for you. If you look at the cell tomorrow, the same picture of yesterday’s date will appear.
This is useful when you keep a logbook for your travels for example that you need to update daily. You need to enter new data every day, adding to the existing data, and each record relates to a certain date. (You can see how the Today formula would not be useful here; all the records will have the same date!)
It is therefore important to understand the difference these two functions, and think carefully how you want to use it before deciding on how to appropriately apply these techniques.
And if you need to enter the time in an excel cell quickly, the short cut is “Control” + “Shift” + “Semicolon”
That is all for today, I hope that this post helps you to use dates more efficiently in Excel :)
Hi, and welcome to my blog :) I am an Excel enthusiast and want to help others keen on improving their own Excel skills. I hope you learn something useful on my blog.