Even the most ardent users of Microsoft’s services can sometimes be unaware of some of the really useful tips. One such useful feature is the ability to put today’s date in Microsoft Excel with just a few keystrokes.
What do you need to add today’s date in Microsoft Excel
Microsoft Excel is a powerful processor that makes it easy for many users to work with large amounts of tabular data on a daily basis. However, even experienced professionals who have worked with this program for many years sometimes get lost when they need to insert the current date and time into a cell of a worksheet. This is mainly due to the fact that the “Date” function, which would logically be used to obtain the desired values, performs a completely different task.
It depends on exactly how you want to add dates to Excel, as it has several ways to do it. For example, you can enter today’s date in some reports or invoices. You can also insert a date in Excel that will update automatically and always display the current date and time. You can also automatically fill in the days of the week or enter arbitrary dates in the worksheet.
Well, here’s how to add today’s date to your Microsoft Excel document.
What types of dates are there
Excel considers two options for the “Current Date” value.
The first is a fixed value of the current date and time stored on the user’s personal computer. Once entered in the worksheet, the value won’t change regardless of changes to the actual date and time. This option may be needed, for example, when you store the date when an employee is hired or fired, or when you enter the date when goods are received in the warehouse. These values should be static because they don’t change over time.
The second variant of the “Current date” value is dynamic, changeable, and updatable. The most common use of this variant is the date and time value in the corner of the worksheet, as part of the inscription “Today May 14, 2017”. This option is commonly used in formulas, for example, to calculate how many days have passed since a certain date. An HR person can find out if an employee’s probationary period is up, and a warehouse worker can check to see if a batch of goods is past its expiration date.
Of course, dynamic date and time in Excel are very useful, but there’s a very important nuance – there’s no continuous updating of these values. The value entered will change when you re-open the spreadsheet, when you run a macro, and when you calculate various formulas.
If you just leave the spreadsheet open and do nothing of the above, the date and time will remain the values you entered once. However, as soon as you reopen the spreadsheet or recalculate the formula, the values will be updated.
How to enter a date in Excel
There are many ways to enter a date into an Excel cell, such as 1/1/2016, or 1-Jan-2016, or 1-Jan, or January 1, 2016. The Microsoft system will understand after you enter that you’re entering a date, and will automatically apply the date formatting to that cell. Quite often Excel will format the newly inserted date according to the default date settings in Windows, but sometimes it can leave it exactly as you entered it.
Sometimes, Excel fails to recognize your input as a date and you see it left-justified in a cell, in this case, you should try to insert a date in a different format that is close to your default short or long date formats. These formats will be marked with an asterisk (*) in the “Format Cell” dialog window and, by doing this, you can quickly access them on the Excel ribbon (Home tab > Number group).
Those actions would give you the ability to easily change the date format later using the “Format Cells” dialog. To open this dialog, you should press the “Ctrl + 1” shortcut.
Take a note, it may happen that a date you’ve entered displays as a number of pound signs (#####) in a cell, often it means that the cell is too narrow to fit the entire date. The solution is to double-click the right border of the column in order to auto-fit the date or drag the right border to set the desired column width.
How to insert today’s date and the current time in Excel
Microsoft Excel gives its users an opportunity to enter the current date and time either as a static or dynamic value.
How to enter today’s date in Excel (as a timestamp) using the shortcuts
Before entering a date, you should understand what “timestep” is. Timestamping means entering a “static date” that won’t be changed with the course of time or when the spreadsheet would be recalculated.
So, to enter the current date and/or time as a static value that won’t automatically update the next day, you follow the tips below:
- The “Ctrl + ;” shortcut will show today’s date in a cell.
- The “Ctrl + Shift + ;” shortcut inserts the current time.
- In order to enter the current date and time, press “Ctrl + ;”.
- Then press the Space key, and then “Ctrl + Shift + ;”.
How to enter an automatically updatable today’s date and current time
If you want to input today’s date in Excel that will always stay up to date, you should use one of the Excel date functions which are displayed below:
- “=TODAY()” – inserts today’s date in a cell.
- “=NOW()” – inserts today’s date and the current time in a cell.
Unlike Excel date shortcuts, the TODAY and NOW functions will always return today’s date and current time.
While using the Excel date functions, you shouldn’t forget that:
- The returned date and time won’t refresh continuously, they would be updated only after the spreadsheet will be re-opened or re-calculated, or when a macro containing the function will be run.
- The functions take the current date and time from your PC’s system clock.
Hope, those tips will help to understand Excel better.