inserting date on line

[ExcelRoy]

hello,

please can you help?

i have a spreadsheet for entering parts ordered

simple list of parts, but would like a column to show the date ordered without entering it

i.e. when i open sheet and enter a line i would like the date to automatically appear in say column F (to save time)

but

it needs to save it as that date so next time i go into the sheet it still shows that day and not the new day until i enter a new line on that day

i was thinking along the lines of =now() but this only keeps the day that the shet is opened

hopefully i made this clear

any help would be appreciated

kind regards

 

[Richard Schollar]

Hi Roy

Not exactly what you were after, but did you know that if you click Ctrl+; in a cell, then the date is automatically entered? It is a fixed value, so does not change unless you change the date.

Best regards

Richard

 

[ExcelRoy]

yeah thats cool, but i was thinking of as many time saving ways as possible because i have so many values to enter i would like it to save the date without entering it (lazy eh?)

thanks anyway

 

[Richard Schollar]

Ok, this is one way of doing it: you need to place the following code in the sheet module of the sheet that you enter the data into:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A:A")) Is Nothing Then
    If Cells(Target.Row, "F") = "" Then Cells(Target.Row, "F").Value = Date
End If
End Sub

Basically, any time you enter a value or do anything to a column A cell, the macro will fire and will insert a date in the corresponding F cell but only if it is currently empty.

Richard