Excel - Date & Time Stamp

L

learning_codes

Hi,

I would like to know if there is a way to set up when I enter the data
each cells on first row, it will default the date and time stamp on
first row. 2nd row should have the different date and time stamp than
1st row.

I put "=now()" but it create the same date and time for 80 rows. I
don't want that. I want to enter each rows and each rows should be
different by one minute apart but same date - June 22.

Your help would be much appreciated.

Thanks
 
G

Gord Dibben

Format A1:A79 as Custom dd-mmm-yy h:mm

Select A1

Hold down CTRL Key and hit semi-colon key then <space> bar.

Still holding CTRL key hit SHIFT key and semi-colon key.

Select A2 and enter =A1 + 1/1440

Drag/copy down to A79


Gord Dibben MS Excel MVP
 
L

learning_codes

Thanks for the information. I tried and it works.

I need to know if there is a trigger when I enter "Title" in A5 and the
code create the date stamp on A1.

Will that be possible ??

Thanks
 
G

Gord Dibben

You will need event code.

But if you use A5 as a trigger cell how can you fill A1:A79 with date/time as
first desired?

Maybe use a cell in Column B as a trigger cell.

Try this code in your worksheet.

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo CleanUp
Application.EnableEvents = False
If Target.Address = "$B$1" Then
If Target.Value = "Title" Then
Range("A1").Value = Format(Now, "dd mmm yyyy hh:mm:ss")
End If
End If
CleanUp:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code". Copy/paste the code into that
module.


Gord
 
Top