In a previous tip I showed you that you can input dates three different ways into a spreadsheet. One of them was the formula that allowed the result change every day, automatically for you. This is the one I will concentrate on today. Firstly, I want to tell you that your PC has an internal battery that keeps your internal clock ticking. If you are attached to the network then the date and time comes from the server you are attached to. If the battery in the pc or server is dying the date and time is wrong so all your calculations are wrong. Therefore, if using these formulae for date and time in your spreadsheets, it is wise to check your computer is in date every so often by typing the second formula in this tip in an empty cell and then deleting it once you know its correct.
There are two formulae to be used here. The first is –
“=Today()”
This is the formula that results in the date appearing in your cell or formula.
You can use this to have a daily calculation of how many days there are left to an event or after the event. This is how:
You can either type it all in one cell as a long formula or break it down into several cells. This of course depends on what is in your spreadsheet and if you need to use several different dates.
- Start with typing a start date in a cell e.g. 1/1/19 in cell A1
- In cell C1 type the following to calculate how many days have past, since the start of the year. “=today()-A1.”
The result should be 42 as my date today is 12/2/19. But my result may well look rather different, like this. 
This is because you are using a formula that is calculating dates and therefore the result will naturally be in the format of a date.
Change the format to numbers. The quickest is using the comma tool on the Home tab. 
Your answer will look like this. 
Every day this will change and grow as the formula recalculates when you open the file again. If you never close the file or do any new calculations in it you may wish to force the recalculation by either using the F9 key on your keyboard. Or the Refresh All tool in the data tab. 
OR use the Recalculate now tool in the Formula Tab. 
You do not have to have the formula of “today ()” inside the formula. This too could be in a cell on its own and referenced in the formula. Therefore, you can see that the date is correct.
e.g. 
Notice that the cell A1 has a large number in it. This is because I have used the tool “Show Formulas” found in the Formula Tab. 
Excel started counting days from the 1st of Jan 1900. There are 43,466 days from that date to today as of 12/2/19.
The second formula is “=Now()” which will result in this. 
Have fun with these formulae. Its great if you like to keep a very close eye on how old your friends are! 😊
