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.

Functions

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

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.

Syntax

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:

1. TODAY() is a volatile function, which means it is constantly updated whenever a worksheet is opened or edited.
2. If we need to insert the current date and time, we have to use another function named NOW() instead of TODAY().
3. If a TODAY formula does not update automatically, our workbook probably has automatic recalculation turned off. In order to turn it on again, we have to go to the Formulas tab> calculations options and select Automatic from the drop-down menu.

NOW Function

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.

Syntax

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.

1. On a worksheet, we must first; pick the cell in which the current date should be entered.
2. In order to insert the current date, we have to enter the Ctrl+;

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”.

1. On a worksheet, first we have to select the cell in which we need to insert the current date.
2. Next, to insert the current date as a dynamic date, we have to enter the below function into a vacant cell and then press Enter key:

=TODAY()

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:

=NOW()

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.

Months:

• m: – month number that doesn’t start with a zero.
• mm: – month number that starts with a zero.
• mmm: – Short form of the month name, such as Feb.
• mmmm:- Full form of the month name, such as February.
• mmmmm: – Month as the first letter, such as J stands for June and July.

Days:

• d: – Days number that doesn’t start with a zero.
• dd: – Day number begins with zero.
• ddd: – Abbreviated day of the week, for example, Mon.
• dddd: – Complete name of the day of the week, for example, Monday.

Years

• yy: – Two-digit year
• yyy- Four-digit year

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:

• “mm/dd/yyyy” – The format of date, which is used in the USA, shows as 06/04/2021.
• “dd/mm/yyyy” – The format of date, which is used by the rest of the world, shows as 04/06/2021.
• “dd-mmm-yy” – Shows as 06-April-21.
• “dddd, mmmm d, yyyy” – Full date, including the day of the week, shows as Tuesday, April 06, 2021

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: