People store their necessary information in Excel. Business sectors, multinational companies, and educational institutions tend to keep employees’ track records in Excel workbooks. In that case, the Date entry becomes essential. People may find it a bit difficult to input Date in Excel. That’s because Excel has some specific formats for the date. Again, you may encounter that the Date entries get updated every time we open the workbook. That means the date is dynamic, which is not desired most of the time. So, in this article, we’ll show you the simple ways to Insert Static Date in Excel.
Download Practice Workbook
Download the following workbook to practice by yourself.
4 Simple Methods to Insert Static Date in Excel
Static Date means the date won’t change every time we open the Excel workbook. To illustrate, we’ll use a sample dataset as an example. For instance, the following dataset in the below picture has some Orders. Here, we’ll insert the Date for each order. There are some effective ways to input the date. We’ll show them all clearly. Therefore, go through the below methods carefully to learn how to Insert Static Date in Excel.
1. Apply Keyboard Shortcut in Excel to Insert Static Date
We can perform various operations in Excel using the keyboard keys. They save us a lot of time and effort. But first, you’ve to know which key or a mix of keys represents which operations. In our first method, we’ll apply the Keyboard Shortcut. So, follow the steps below to perform the task.
- First, select cell C5 or any other cell where you want to insert the date.
- Then, press the Ctrl and ; keys at the same time.
- As a result, a date will appear in cell C5. This date is from your computer system. Excel looks for the Date present in your system.
- In this way, you can insert the date for other orders too and they are static.
2. Input Static Date with TODAY Function & Keyboard Function Keys
Additionally, if you want to input the date of that specific day, you can use the TODAY function. This function will return the date of that specific day you insert the function. But there’s a problem. This function is dynamic. The date will update by itself whenever you open the worksheet. However, there’s a way to make it static. Therefore, learn the following steps to carry out the operation.
- Firstly, choose cell C5.
- After that, type the formula:
- Subsequently, press Enter to return the date.
- Again, select cell C5.
- Now, press the F2 function key and release it.
- Next, press the F9 key and release it.
- Consequently, press Enter.
- Finally, it’ll return the Date only without the formula. Thus, the date becomes static.
- See the image below to understand better.
- Like this, insert the date for every order when they arrive.
- How to Insert Excel Timestamp When Cell Changes Without VBA (3 Ways)
- Insert Timestamp in Excel When Cell Changes (2 Effective Ways)
- How to Insert Excel Date Stamp When Cells in Row Are Modified
- Automatically Insert Timestamp Data Entries in Excel (5 Methods)
3. Combine Excel TODAY & IF Functions for Inserting Static Date
We know Excel provides many different features and functions to perform various tasks. In the previous method, we applied the TODAY function. But the function alone is not static. We had to use the function keys. In this method, we’ll use the TODAY function along with the IF function. Moreover, we’ll create a formula with circular reference. So, we need to make some changes to the Excel File Options first. Otherwise, it’ll return an error. Hence, follow the process to perform the task.
- First of all, go to File ➤ Options.
- As a result, the Excel Options dialog box will pop out.
- In the Formulas tab, check the box for Enable iterative calculation as marked below.
- Afterward, press OK.
- Now, select cell C5 and insert the below formula:
- Next, press Enter.
- Consequently, input anything in cell B5. For this example, input Entry 1.
- Immediately, today’s date will emerge in cell C5. And whenever you open this worksheet, the date won’t change.
- Use the AutoFill tool for cells below C5.
- From now on, a date will appear in the C column cells every time you input something in the adjacent cell in column B.
- In this manner, you can insert the Static Date in Excel.
4. Insert Static Date Using DATE Function in Excel
Lastly, we’ll make use of the DATE function in Excel. This function is very easy to use. You just need to type the year, month, and date in the argument section. And this function is not dynamic. So the date won’t change. Now, learn the process to Insert a Static Date using the DATE function.
- Select cell C5 at first.
- Type the following formula:
- Then, press Enter.
- Hence, it’ll return your desired date.
- Similarly, insert the date for other cells.
- At last, you’ll get your required outcome.
Read More: Excel VBA: Insert Timestamp When a Macro Is Run
Henceforth, you will be able to Insert Static Date in Excel following the above-described methods. Keep using them and let us know if you have more ways to do the task. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.
- How to Convert Unix Timestamp to Date in Excel (3 Methods)
- Convert Active Directory Timestamp to Date in Excel (4 Methods)
- How to Convert SAP Timestamp to Date in Excel (4 Ways)
- Convert Timestamp to Date in Excel (7 Easy Ways)
- How to Remove Timestamps from Date in Excel (4 Easy Ways)
- How to Add Date and Time in Excel When Printing (3 Easy Ways)
Hi there, I am using the 4th method to record a static timestamp when someone inputs some data into a separate column. This acts as a timestamp logging feature for my query to sort based on. However, it produces a circular reference error when copying said sheets with the formula as new copies. Are you experiencing this as well?
Greetings from our website! Thank you for posting your question on the platform. I have yet to experience the issue you described personally. However, I can offer some insights to help you troubleshoot the problem.
The circular reference error may occur because the formula references the cell containing the procedure, creating an infinite loop. To avoid this, you can modify the system to refer to a different cell that doesn’t have the formula.
Excel’s built-in Error Checking feature can help identify and resolve circular references. To access this feature, go to the Formulas tab in the Excel ribbon, click Error Checking, and then click Circular References. Excel will then highlight any circular references in your workbook and suggest resolving them.
Providing any prominent solution without glancing at the workbook is challenging. You can share your workbook with us via Exceldemy Forum to better understand your situation.
Lutfor Rahman Shimanto (ExcelDemy Team)