How to Insert Static Date in Excel (4 Simple Methods)

The following dataset has some Orders. We’ll insert a static Date for each order.


Method 1 – Apply a Keyboard Shortcut in Excel to Insert a Static Date for Today

Steps:

  • Select cell C5 or any other cell where you want to insert the date.
  • Press the Ctrl + ; keys at the same time.

Apply Keyboard Shortcut in Excel to Insert Static Date

  • Today’s date will appear in cell C5 as per your system settings.
  • The date is inserted as a value, so it won’t change.
  • Repeat for other cells if needed.

Apply Keyboard Shortcut in Excel to Insert Static Date


Method 2 – Input a Static Date with the TODAY Function and Keyboard Function Keys

Steps:

  • Choose cell C5.
  • Insert the formula:
=TODAY()
  • Press Enter to return today’s date.

  • Select cell C5 again.
  • Press F2, then press F9.

  • Press Enter.
  • This will return the Date only without the formula, so it becomes static.
  • Repeat for other cells if needed, or copy the value.


Method 3 – Combine Excel TODAY and IF Functions for Inserting a Static Date

Steps:

  • Go to File and Options.
  • The Excel Options dialog box will pop out. In the Formulas tab, check the box for Enable iterative calculation.
  • Press OK.

Combine Excel TODAY & IF Functions for Inserting Static Date

  • Select cell C5 and insert the following formula:
=IF(B5<>"",IF(C5="",TODAY(),C5),"")
  • Press Enter.

Combine Excel TODAY & IF Functions for Inserting Static Date

NOTE: This formula returns the output of TODAY() if both of the cells C5 and B5 are blank beforehand. The IF function tests that condition.
  • Input anything in cell B5. For this example, input Entry 1.
  • Today’s date will emerge in cell C5. Whenever you open this worksheet, the date won’t change.

Combine Excel TODAY & IF Functions for Inserting Static Date

  • Use the AutoFill tool for cells below C5.
  • A date will appear in the C column cells every time you input something in the adjacent cell in column B.

Combine Excel TODAY & IF Functions for Inserting Static Date

Read More: How to Remove Time from Date in Excel


Method 4 – Insert a Static Date Using the DATE Function in Excel

Steps:

  • Select cell C5.
  • Insert the following formula:
=DATE(2022,6,15)
  • Press Enter.
  • This’ll return your desired date.

  • Repeat for other cells.

Read More: Formula for Weekly Dates in Excel


Download the Practice Workbook


Related Articles


<< Go Back to Insert Date | Date-Time in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Aung Shine
Aung Shine

Aung Shine completed his bachelor’s in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. It has been almost 2 years since he joined SOFTEKO and actively working on the ExcelDemy project. Currently he works as a Team Leader where he guides his team members to create technical content. He has published 150+ articles and reviewed 50+ articles. He has also solved various user problems before. He has interests in Data Analysis, Power Query, Advanced Excel,... Read Full Bio

2 Comments
  1. 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?

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Mar 23, 2023 at 1:37 PM

      Hello KEVIN

      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.

      Regards
      Lutfor Rahman Shimanto (ExcelDemy Team)

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo