How to lock date in excel when with today function so it will not change when i open file on a new day

[BradyKeller11]

Hello!

Please help me out if you can....

This is my formula: =IF((G9)="x",(TODAY())," ")

It currently will add the date to the cell with this formula in it when i enter a X into cell G9 and and the formula cell will be blank with nothing in it.

I access this file almost daily and i want to keep the dates previously entered to not change as i enter other dates.

How can i lock dates with this formula so they will not change when i save and go back into the file at a later date?

 

[JoeMo]

You can't lock the date produced when the worksheet function TODAY() is first calculated, so a worksheet function solution is not possible. You would need VBA to enter the date, in the cell where your formula is (and replacing the formula), when an "x" is placed in cell G9. Would you be interested in a VBA solution? If yes, do you want to monitor all col G cells for the insertion of "x", and if inserted where do you want the insertion dates stamped?

 

[BradyKeller11]

JoeMo,

Thanks for the reply.

I would like to see your VBA formula. I do have other dates in the G cells but no all. For this on my TODAY() formula I would replace "G9" with the other corresponding cells i.e.: G11, G14, etc. individually. I'm curios to see what you can come up with that could accomplish my goal.

Regards,

Brady

 

[JoeMo]

VBA is NOT a formula, it's code that will produce a date stamp any time a value in col G is changed to "x". Can you answer my question about where to place the date stamps (i.e. in what cell(s)were you placing your formula from post #1 ) ? Even better, can you also provide the cell addresses in col G you want to monitor?

 

[BradyKeller11]

The date would then need to be stamped in cell I9.

Below are all formulas that need to be stamped.

When an X is placed in a "G" column I need the date to be stamped in the "I" column that's next to it.

G4 - I5
G5 -I5
G9 - I9
G10 - I10
G11 - I11
G12 - I12
G16 - I16
G17 - I17
G22 - I22
G23 - I23

 

[JoeMo]

This is sheet code for the worksheet you want to date stamp.
To install sheet code:
1. Right-click the worksheet tab you want to apply it to and choose 'View Code'. This will open the VBE window.
2. Copy the code below from your browser window and paste it into the white space in the VBE window.
3. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
4. Make sure you have enabled macros whenever you open the file or the code will not run.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
If Not Intersect(Columns("G"), Target) Is Nothing Then
    Application.EnableEvents = False
    For Each c In Intersect(Columns("G"), Target).Cells
        If Ucase(c.Value) = "X" Then
            Cells(c.Row, "I").Value = Date
        Else
            If IsEmpty(c) Then Cells(c.Row, "I").Value = ""
        End If
    Next c
    Application.EnableEvents = True
End If
End Sub

 

[BradyKeller11]

JoeMo,

I really appreciate the help! it works great!

-Brady

 

[JoeMo]

JoeMo,

I really appreciate the help! it works great!

-Brady

You are welcome - thanks for the reply.

 

[chasenmbrs]

This is for JoeMo.

In the previous response with the VBA code, you set a condition for the text "X". Is there a way to set the condition for ANY text? Or can I set it for any of multiple valid text input?

In my case, we have numerous people that use the sheet, each with their own set of initials that would be considered valid.

I tested the VBA for a single set of initials and it works GREAT! Thank you.

 

[Fluff]

Maybe

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Dim St As String
St = "AB|XY|FG|HJ"
If Not Intersect(Columns("G"), Target) Is Nothing Then
    Application.EnableEvents = False
    For Each c In Intersect(Columns("G"), Target).Cells
        If InStr(1, St, c.Value, vbTextCompare) >= 1 Then
            Cells(c.Row, "I").Value = Date
        Else
            If IsEmpty(c) Then Cells(c.Row, "I").Value = ""
        End If
    Next c
    Application.EnableEvents = True
End If
End Sub

Change values in red to suit, but leave the pipes