Excel formula to set a static date.

Question

Excel formula to set a static date.

Anything Cabinets Admin 20

Hi I need some assistance with an excel formula please.

I am using excel as an order book and need to record the date an order was placed.

I am using =IF(COUNTIF(H5:AJ5, "x")>0, TODAY(), "") to populate the current date into cell G4 when an "x" is placed in either of the fields, H to AJ but G4 remains blank if there is anything else in those fields.

The problem I am having is that the date does not remain static and keeps updating to the current date when the spreadsheet is reopened.

I have found another formula to set the date as static =IF(A1="", "", IF(B1="", TODAY(), B1)) however, when I mesh the two together with the bits that I need the word FALSE is entered into the G4, instead of being blank, like I need it to be.

Can someone help me with the formula that I need as I am trying to make it as easy as possible for non-excel users to fill in the spreadsheet?

Thank you in Advanced

Answer accepted by question author

1 additional answer

Your answer

Answer 1

Dear Anything Cabinets Admin

Thank you for submitting your inquiry on the QA forum.

In Excel, a static date is one that doesn’t change — unlike formulas like =TODAY() or =NOW(), which update every time the file is opened or recalculated. Static dates are useful for things like order dates, timestamps, or logging data.

The easiest way to enter a static date is by using the keyboard shortcut:

Press Ctrl + ; (Control + semicolon) This enters the current date as a fixed value that never changes.

If you want Excel to insert a static date automatically, you can use a simple VBA macro. For example, you can set it to insert today’s date in column B whenever you type in column A.

There’s also a workaround using circular references, but it's not recommended due to complexity and instability.

In short, for most users, manually entering the date using **Ctrl + ; **is the most reliable way to set a static date in Excel.

Answer 2

Tammy-Ng 5,510 Microsoft External Staff Moderator

Dear Anything Cabinets Admin
Thank you for submitting your inquiry on the QA forum.

Based on the information you’ve provided, I understand that you are seeking an optimal solution for your Excel file. However, the current details appear to be incomplete, which limits my ability to test and provide a precise recommendation.

To assist you effectively, I kindly ask that you share a sample of your data structure. This will enable me to better understand your requirements and propose a tailored solution.

Additionally, I noticed that your post includes the "Android" tag. Could you please clarify whether this issue is related to a mobile device, or if the tag was added inadvertently?

I look forward to your response and remain committed to supporting you.

Anything Cabinets Admin 20 Reputation points

2025-09-12T02:53:32.5866667+00:00

Sorry the Android tag was selected incorrectly. I tried to attached a sample of the excel spreadsheet, but it wouldn't let me attach an file with an xlsx extension, so I have attached a screenshot of what the form is doing with the formula in column G.

If I chnage the "1" to an "x" today the formula enters todays date, however, once I close the spreadsheet and reopen it tomorrow the date will be changed to 13/09/25 instead of retaining the 12/09/25 like I need it to.

Does that make sense?
Tammy-Ng 5,510 Reputation points Microsoft External Staff Moderator

2025-09-12T02:58:43.7233333+00:00

Dear Anything Cabinets Admin
Thank you for your response, your input is very important to me.

If possible, please share a screenshot showing how the form behaves with the formula in column G, along with a sample of the data you're working with. This will help me better visualize the structure and understand the issue in detail, so I can provide you with the most effective support.

Looking forward to your update.
Anything Cabinets Admin 20 Reputation points

2025-09-12T03:20:28.4966667+00:00

Screenshot 2025-09-12 125056.jpg

Sorry I thought I had attached it, but here it is.
Tammy-Ng 5,510 Reputation points Microsoft External Staff Moderator

2025-09-12T04:17:34.9933333+00:00
Dear Anything Cabinets Admin
Thank you for your response!

Based on the screenshot, details you shared, and my own testing in a test environment, here’s a tailored VBA solution to fix your static date issue (so the date stays fixed and doesn’t update on file reopen):

Step 1: Open the VBA Editor

Press Alt + F11 to launch the VBA Editor.

On the left navigation pane, select your target worksheet (e.g., Sheet1 — match the sheet name where your order book is stored).

Step 2: Paste the VBA Code

Copy and paste this code into the code window (right pane) for your worksheet:

Private Sub Worksheet_Change(ByVal Target As Range) Dim CheckRange As Range ' Define the range to monitor: Columns H→N, from row 8 down to the last used row in column H Set CheckRange = Me.Range("H8:N" & Me.Cells(Rows.Count, "H").End(xlUp).Row) ' Only run if the edited cell is in the monitored range (H8:N) If Not Intersect(Target, CheckRange) Is Nothing Then Dim RowNum As Integer RowNum = Target.Row ' Get the row number of the edited cell Dim DateCell As Range Set DateCell = Me.Range("G" & RowNum) ' Target cell in Column G for the static date ' Auto-set today's date ONLY if: ' 1. The edited cell is "x" or "1" ' 2. The date cell (Column G) is still blank If (Target.Value = "x" Or Target.Value = "1") And DateCell.Value = "" Then DateCell.Value = Date ' `Date` = static today's date (won't update on re-open) End If End If End Sub

Step 3: Save and Prepare the File

Save as a Macro-Enabled Workbook (*.xlsm):

Go to File > Save As

Choose Excel Macro-Enabled Workbook (*.xlsm) from the "Save as type" dropdown

Save the file (this format is required to run macros).

Close the VBA Editor, then reopen the saved .xlsm file.

Step 4: Test the Functionality

Enter "x" or "1" into any cell in H8:N8 (or rows below, as long as column H has data).

The corresponding cell in Column G will auto-populate with today’s date — and this date will stay static (it won’t update when you reopen the file later).

Next Steps

Feel free to adjust the code for your needs:

Change the monitored range: Update H8:N to H8:O (for more columns) or H5:N (for earlier rows).

Add trigger values: Modify Target.Value = "x" Or Target.Value = "1" to include "X" or "yes".

If you run into errors or need further tweaks, share the details, and I’ll help you resolve them!

If my initial answer has been even partially helpful, please consider marking it as “Accepted Answer” so others facing similar issues can find it more easily.
Anything Cabinets Admin 20 Reputation points

2025-09-12T04:27:21.0233333+00:00

Thank you very much I will try that out this afternoon and see what happens on Monday when I open up my test spreadsheet.
Tammy-Ng 5,510 Reputation points Microsoft External Staff Moderator

2025-09-12T04:31:25.93+00:00

Dear Anything Cabinets Admin
You're most welcome! That's excellent, I'm glad to hear you'll be testing it out this afternoon.

I'm certainly looking forward to hearing about your findings on Monday, especially regarding how the static date behaves after reopening your test spreadsheet. I'm optimistic this VBA solution will address your issue perfectly.

In the meantime, if my initial answer proves to be helpful, even partially, I would truly appreciate it if you could consider marking it as the 'Accepted Answer'. This helps other users who might be facing similar issues to find the solution more easily.

Please do keep me updated!
Anything Cabinets Admin 20 Reputation points

2025-09-14T21:29:46.3133333+00:00

Unfortunately Tammy the VBA it didn't.

Tammy-Ng 5,510 Microsoft External Staff Moderator

Dear Anything Cabinets Admin
Thank you for your response, your answer is very important to me.

Based on the information you provided, I’ve made a small adjustment to the macro code. Please try running it again and let me know how it goes.

Updated VBA Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim CheckRange As Range
    Set CheckRange = Me.Range("H5:AJ5")
 
    If Not Intersect(Target, CheckRange) Is Nothing Then
        Dim RowNum As Long
        RowNum = Target.Row
        Dim DateCell As Range
        Set DateCell = Me.Range("G" & RowNum)
 
        If (Target.Value = "x" Or Target.Value = "1") And DateCell.Value = "" Then
            DateCell.Value = Date
        End If
    End If
End Sub

I look forward to hearing your feedback. Warm thanks!

Anything Cabinets Admin 20 Reputation points

2025-09-15T21:43:29.3633333+00:00

Unfortunately that didn't work either I might just have to use Hanry's suggestion with the Ctrl +; to set the date instead of getting a formula to do it automatically.

Thank you very much for your help though it is greatly appreciated.

Share via

Excel formula to set a static date.

1 additional answer

Your answer