How to Insert Date in Excel?
In excel, every valid date will be stored as a form of the number. One important thing to be aware in excel is that we have got a cut-off date which is “31-Dec-1899”. Every date we insert in excel will be counted from “01-Jan-1900 (including this date)” and will be stored as a number.
Examples
Here we will learn how to insert date in excel with the help of the examples below.
Example #1 – Date Stored as a Number
Take a number of 50, 100 & today’s date i.e. 30/01/2019 in the excel sheet.
Now, we can observe the way data is stored in excel when we change the formatting of the above data to date & accounting format.
50 – Change format to short date
100 – Change format to short date
30/01/2019 – Change format to accounting as it already in date format
Here if we observe the number 50 has been changed to date and it displayed exactly 50 days from 01/01/1900 (including this date in the count). Similarly, for the number 100 the date displayed is the exact count from 01/01/1900. And the third observation which is already in date format and we changed to number format is displaying “43,495” which says that today’s date i.e. 30/01/2019 is exactly 43,495 days away from the cut-off date.
Example #2 – Inserting Specific Date in Excel
In order to insert a specific valid date in excel, we have to use DATE().
In the above function, we can observe that DATE is asking to provide values of Year, Month, Day. As we give the details of it then this displays the date in default format as below:
In the above example, we had given the year as 1992, month as 10 and day as 30. But the output is displayed as per the default format.
Example #3 – Changing the Inserted Date Format in Excel
As we have seen in our earlier examples, we have got the date displayed in a pre-defined format. In order to change the format of the date, we should go to format cells. Let’s see how it can be done:
In order to access the format cells, we should Right-click on the date cell and then the above list of operations will pop out. Here select the format cell which will take to “Format cells” window.
We got the list of a different format for the date as above. Let’s select one of the formats and see how the format got changed as below.
This is an important feature of formatting which helps in selecting a date as per their required format for different organizations.
Example #4 – Insert List of Sequential Dates in Excel?
If we want to list out of the sequence of dates, then we can simply do it by selecting the start date and drag it down till you reach the end date as per your requirement.
Insert the date manually (don’t use DATE()).
And drag it down as below
Here we got the list of dates in a sequence from the starting date.
Example #5 – Insert Dates with NOW() and TODAY() Excel Function
In order to get the present day date we can use TODAY() and get the present day along with current time then we should go with NOW() Function. Let’s see the example below:
We also got a keyboard shortcut instead of the formulas.
To get the present date we should use Alt + ; shortcut instead of TODAY()
To get the present date along with time, we should use Alt + Shift + ; shortcut instead of NOW()
Example #6 – How to Extract Selective Information from the Inserted Excel Date Values.
There are three important functions in excel which help us to extract the specific information from the date. They are : –
Example #7 – Using TEXT() for Inserting Dates in Excel
TEXT() is one of the very important formulae for a presentation of data into a certain desired custom format.
Let’s assume the dates as per above example and we can get the day, month & year and per the formats that are mentioned in the 3rd column.
By using the TEXT() as above we can derive as per the format we required.
TEXT() is also used in changing the format of the date to as per our requirement. By this, we can avoid the steps to go to format cells and then change the format. This will also reduce the time consumption when it comes to changing the format.
Let’s see how can we change the format using the TEXT().
TEXT() will also help us in concatenating with a date. When we try to concatenate without using the TEXT() then it will display the number instead of date as below:
By using TEXT(), we can concatenate with an actual date as below:
How to Change the Format of the Inserted Date in Excel?
If we observe from the above example, the date is in the form of MM/DD/YYYY. Suppose if we want to change the format then we can do it as per below:
We should go to the control panel then select ease of access. We can visualize the option of clock, language & region.
Click on the above option and you will pop out to other windows where you get an option of the region and go on that.
Here we can go on and select the date format as per our requirement i.e, short date or long date and this will be the default setting of date once we apply it. In case you want to go back to the previous format then we can reset it on the same window.
Things to Remember
Inserting a valid date in excel should be always stored as a number. We can verify this condition by using ISNUMBER().
Let us take the date in two different formats as shown above. As we know that inserting a valid date in excel should always be stored in the format of the number so we need to verify the same.
As we check for the number using ISNUMBER(), the date which is stored in the form of the number and a valid date will be “TRUE” or else “FALSE”.
In our example above :
- 30/01/2019 – This is TRUE as it is stored in the form of a number and this date is valid
- 30.01.2019 – This FALSE as it is not stored in the form of the number and it is not valid.
Recommended Articles
This has been a guide to Insert Date in Excel. Here we discuss how to insert dates in excel along with the top 7 examples using a combination of DATE(), NOW(), TODAY(), and TEXT() Function. You may also look at these useful functions in excel –
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
