Static Date Formula or VBA

[Grammarjunkie]

Hey guys,

I'd really like a formula (I'm thinking this will require VBA-thing though) that would populate an unchanging date.

For instance, I want F2 to populate with =NOW(), when I manually fill G2 with blah blah, but I want the NOW() to not change when I open the file the next day.

So essentially column F will automatically fill with the time and date (that doesn't change) when the corresponding row in column G is filled.
(If it matters, G fields would be manually filled with a percentage.)

Thanks for any help!

 

[oriwitt]

Try this.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 7 And Target.Row > 1 Then
        If Target.Value <> "" Then
            Application.EnableEvents = False
            With Target.Offset(, -1)
                .Value = Now
                .NumberFormat = "dd/mm/yy hh:mm AM/PM"    ' change as required
            End With
        End If
    End If
    
    Application.EnableEvents = True
    
End Sub

@oriwitt has the right idea but that is entering date-like text into an excel cell which will auto-format it as it sees fit.
It would be better to format the Range that the text is to be entered into. e.g. in the Sheet Code Module enter

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 7 Then
    With Target.Offset(, -1)
        If .Value = "" Then
            .NumberFormat = "dd/mm/yy h:mm AM/PM"
            .Value = Now
        End If
    End With
End If
End Sub

These two are great. Grammarjunkie, I'd suggest you use one of them.

 

[Grammarjunkie]

These two are great. Grammarjunkie, I'd suggest you use one of them.

Thee were immensely helpful and exactly what I needed!

Thank you, all!

 

[WhoCanDo]

Actually, the reason why I chose to enter text formatted with / is because I am in Australia and the default format when entering .NumberFormat = "dd/mm/yy h:mm AM/PM" is 04-04-16 9:31 PM.

I seems that locality defines the change of / to -

However, if the above works for you Grammarjunkie then the above are excellent options.