I have been training for a fair few years now, and I still get surprised by what people do not know what Excel can do. When it comes to dates and time some people do not know that they are values that can be used in calculations. Were you aware that you can use a function that gives you todays date in Excel? Were you aware that you can calculate how many days there are between two dates very easily? To these questions I usually get the answer. No.
So where do you get your dates from when you need to use them in Excel? Do you look at the bottom right corner of your monitor to the date and time area?
Finding this do you then type the date into a cell to be used in a referencing formula? Please don’t!
Let’s start by saying you need to type a date or time in a way that Excel will understand them to be what you want them to be. Please use the / or the – between your days and months and years numbers. Using the full stop will result in Excel changing this from a value to a piece of text. (It just looks like a date.)
Different formats of dates and time.
Yes the last entry does have full stops in between. But I would argue that this should never be used as you might be in the situation of someone else updating your spreadsheet. Sees the format of the date but does not know this is not the way the value was typed and replaces that data with a new date but types in the full stops. Thus changing the cell from a date value to a text that cannot be used to calculate.
If you need to find out how many days there are let’s say between the start date of a project and today, follow these easy steps.
- Type the start day in a cell.
- Type the following formula. “=Today()”. There are no spaces in this formula.
Here is what the sheet will look like.
Now type this formula in a new cell.
=C2-A2
This takes the start date away from today’s date. This will update on a daily basis. So please be aware of this fact.
The result will be this.
If your project spans more than one year you need to do the same calculation as before taking the start date away from today’s date, but then you need to divide the result by 365.25 as this is how many days there are in a year taking into account leap years. The result will be in years rather than a few hundred days. The formula would be written like this.
=(C2-A2)/365.25
You need the brackets so that excel calculates the first part first and then the division. Refer to my tip on BODMAS. Also remember that you get a result using base ten not base twelve as is with months, so decrease the decimals to 0.
How to enter todays date into a cell using just keys.
- Hold down the CTRL key and press the semicolon key.
How to enter the time now in a cell using just keys.
- Hold down the CTRL and the SHIFT keys then press the colon key.
Both of these are “static” values. They will not change when the date or time changes. But they can be used to calculate with.
So do have fun and make your life easier by using dates and time correctly in excel.
