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!

 

[WhoCanDo]

What format would you like the date?
31/12/16
Dec 31, 2016
etc.

 

[Grammarjunkie]

What format would you like the date?
31/12/16
Dec 31, 2016
etc.

I'd prefer 04/11/16 9:26 A.M.

 

[WhoCanDo]

Try this. This needs to be copied to the VBAproject sheet that you want it to work with.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'
    If (ActiveCell.Column = 7) Then
        If (Cells(ActiveCell.Row, ActiveCell.Column - 2) = "") Then
            FixedDate = "'" & Month(Now()) & "/" & Day(Now()) & "/" & Year(Now())
            If (Hour(Now()) > 12) Then
                FixedDate = FixedDate & " " & Hour(Now()) - 12 & ":" & Minute(Now()) & " PM"
            Else
                FixedDate = FixedDate & " " & Hour(Now()) & ":" & Minute(Now()) & " AM"
            End If
            Cells(ActiveCell.Row, ActiveCell.Column - 2) = FixedDate
        End If
    End If
End Sub

 

[WhoCanDo]

Not sure why I can't edit my mistake above but this code is corrected

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'
    If (ActiveCell.Column = 7) Then
        If (Cells(ActiveCell.Row, ActiveCell.Column - 2) = "") Then
            FixedDate = "'" & Month(Now()) & "/" & Day(Now()) & "/" & Year(Now())
            If (Hour(Now()) >= 12) Then
                FixedDate = FixedDate & " " & Hour(Now()) - 12 & ":" & Minute(Now()) & " PM"
            Else
                FixedDate = FixedDate & " " & Hour(Now()) & ":" & Minute(Now()) & " AM"
            End If
            Cells(ActiveCell.Row, ActiveCell.Column - 2) = FixedDate
        End If
    End If
End Sub

 

[WhoCanDo]

OK. Can't fix this from my phone. I'll fix it tomorrow. Sorry

 

[WhoCanDo]

OK. Can't fix this from my phone. I'll fix it tomorrow. Sorry

OK, ignore this. My phone access didn't show the correct change but I can see now that the revised code above is correct.

Have you tried it yet?

 

[oriwitt]

It's both easier and harder than that.

The supplied code from WhoCanDo doesn't provide leading zeros for any of the month, day, hour or minute figures as these functions don't return with leading zeroes, and the year function returns as yyyy. You can use the function Format(Now, "MM/dd/yy Hh:Nn AM/PM") which WILL return the date in the correct format, however, it may not be displayed as such depending on system settings.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If (ActiveCell.Column = 7) Then
        If (Cells(ActiveCell.Row, ActiveCell.Column - 2) = "") Then
            Cells(ActiveCell.Row, ActiveCell.Column - 2) = Format(Now, "MM/dd/yy Hh:Nn AM/PM")
        End If
    End If
End Sub

 

[Norie]

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

 

[Teeroy]

@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