Current Date in Excel
There are many situations where we have to enter the current date in a cell. However, we can also type it manually. Sometimes it becomes boring if we need to enter the date often. Fortunately, there are some keyword shortcuts and functions that speed things even more. Let's discuss this in detail.
Current Date Keyword Shortcut
If we enter Ctrl + ; then it will give us the current date into the active cell. In edit mode, this shortcut can also be utilized to include hardcoded data into our formulas.
Current Time Keyboard Shortcut
If we enter the Ctrl + Shift + ; it will give us the current time into the active cell. This shortcut can also be used in edit mode, and it can be used to incorporate a hardcoded date into our formulas.
There are two functions in Excel that will provide us the date and time.
These functions are volatile, which means that any modification to the Excel workbook will prompt them to recalculate. By using the F9, we can also have them recalculate. This indicates that the date and time will always update according to the current date and time.
TODAY function is a very easy function, and in the TODAY function, there are no parameters. Based on the user's PC setting this function will return the current date.
The TODAY function is helpful when we require the current date to be show on a worksheet, regardless of when the workbook is opened. It's also capable of calculating intervals. Suppose we know that somebody was born in 1989. We might use the following formula to calculate their age for this year's birthday.
The above formula uses the TODAY function as a parameter or argument for the YEAR function to obtain the current year and then subtract 1989, returning the age of the person.
This indicates that if this function is included in a worksheet and sent to someone else in a different time zone, the result may differ.
Note: Some important things which we have to know about TODAY function in excel:
NOW function is also an easy function with no parameters. Based on the user's PC date and time settings, this function will return the current date and time. Again, the outcomes will vary depending on the time zone. By applying a number format to the result returned by NOW, we can convert it to a date or a date with time. In excel, a number that represents the current date and time.
Excel Tips-Insert a Static Date or a Dynamic Date
Insert a Static Date
In an Excel worksheet, a static value is once that does not modify when a formula or worksheet is recalculated or accessed.
If we want to insert the current time and keep it static. Then in this situation, we have to enter the Ctrl+Shift+;
Insert a Dynamic Date
In some situations, we may need to insert a date or time whose value is automatically updated. In order to return a dynamic date or time, we can use two functions which are "TODAY" and "NOW".
To insert the current time as a dynamic field, we have to enter the below function into a vacant cell and, then we have to press the Enter key:
How to Change the Formatting of the Date and Time
If the date or time format in the spreadsheet needs to be changed, we can do it. In order to do this, we have to enter the F1, or we can right-click on the cell and click on the option named Format cells. When the Format cells box shows on the screen, we may click on the Number and then select Date or Time and choose the formatting we wish to apply to our spreadsheet.
How to Convert Date to Text Strings in different formats
Excel's TEXT function has no trouble converting dates to text values because they are serial numbers by nature. Specifying the right display formatting for the text dates is probably the most difficult part.
The following data codes are recognized by Microsoft Excel.
In order to show the converted text date precisely the way we need, we can use various delimiters to divide the date codes like comma (,), colon (:), slash (/), dash (-), etc. Below are some examples:
For instance, if we have a column of US dates in Excel and want to export them as to a .csv file for our UK based partner, we can convert the dates to the UK format, as a courtesy:
=TEXT (A1, "mm/dd/yyyy")
Below are Some more formula examples and their results: