time and date

[john tempest]

i need to put the current time in cell (A1) and the current date in cell (D5) when a value is entered in (A3)
thank you
johyn tempest

 

[Von Pookie]

One way:

A1: =IF($A$3="","",NOW())
D5: =IF($A$3="","",TODAY())

However, using formulas, the date and time displayed in the cells will update whenever the sheet recalculates.

If you want the date/time to be static, you need to use a macro.
Try this, which would go in the module of the sheet you want to use it on:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
If Target.Address = "$A$3" Then
    Range("A1") = Time
    Range("D5") = Date
End If
Application.EnableEvents = True

End Sub

 

[john tempest]

this is the actual code i entered.the date it produced was january 1900 and the time was 00:00:00,please help. thankyou john tempest
Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
If Target.Address = "$B$10" Then
Range("A10") = Time
Range("G10") = Date
End If
Application.EnableEvents = True

End Sub

 

[Von Pookie]

Not sure, it worked for me with no problems.

You could try formatting the data within the code:

If Target.Address = "$B$10" Then
    Range("A10").Value = Format(Time, "h:mm")
    Range("G10").Value = Format(Date, "mm/dd/yy")
End If

But maybe it could also be due to the format of the cells themselves; check what they're formatted as.

 

[john tempest]

i got your first code to work. i then tried to expand the range but with no success. the code i used is as follows. please bear with me i am new to all this

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
If Target.Address = "$A$10":"$A$40" Then
Range("A10:A40") = Time
Range("G10:G40") = Date
End If
Application.EnableEvents = True

End Sub

what have i done wrong now
thankyou, john tempest

 

[Von Pookie]

Ah, ok. I think I see what you're trying to do.

You have to be a bit more specific with the code in this case.

Are you looking for something like this?

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

'if the changed cell was within the range B10:B40
If Not Intersect(Target, Range("B10:B40")) Is Nothing Then

    'insert time in column A of the same row
    Cells(Target.Row, "A").Value = Time
    
    'insert date in column G of the same row
    Cells(Target.Row, "G").Value = Date
End If

Application.EnableEvents = True

End Sub

 

[john tempest]

thank you. that code is perfect.
regards John tempest