Aotomatically dating a column in Excel

  Ray5776 19:49 16 Jan 2007

Hi everyone,
I have set up an Excel spreadsheet only 15 columns but that is all I need.
At present I have to make 3 entries, the date in A, a figure in B and a figure in D then Excel does the rest, great stuff.

What I am tring to do now is set it up so that I only have to make an entry in B or D and the date sets in A automatically in the format xx.xx.xx I do not need the time.

When I move down to the next line I need the preceding line to remain at the same date but the new line to have the current date.

Looking into this it appears to be possible and I have experimented with =now() but I need some more guidance yet again.


  Ray5776 19:52 16 Jan 2007

And where to get spelling lessons, sorry I meant Automatic

  VoG II 20:06 16 Jan 2007

The problem with using Today() or Now() is that these values will update automatically when the date or time changes. You need a little bit of code to do what you want:

Private Sub Worksheet_Change(ByVal r As Range)
If r.Count > 1 Then Exit Sub
If r.Column = 2 Or r.Column = 4 Then Cells(r.Row, 1).Value = Date
End Sub

To use this, right click the sheet tab and select View Code. Then copy the code above and paste it into the window that should have appeared. Close that window and try entering a value in column B or D.

  Ray5776 20:52 16 Jan 2007

Hi vog, I cant find the View Code in the sheet tab only "rename" "hide" or "background"
Thanks for your help again.


  VoG II 20:55 16 Jan 2007

View Code should be at the bottom of the list when you right click a sheet tab.

  Ray5776 22:20 16 Jan 2007

i am clicking "format" "sheet" only get as said before no view code I am using right click.


  Simsy 23:54 16 Jan 2007

click "format"...
right click on the sheet TAB, which is at the bottom of the Excel window. It's where the sheets are named... "Sheet1", "Sheet2" etc.



  Simsy 00:12 17 Jan 2007

is a shot of what it looks like...

click here

Hope it helps,



  Ray5776 17:54 17 Jan 2007

Thanks Simsy and Vog, nearly there now I just need the date to display in the format xx.xx.xx as opposed
to xx/xx/xxxx.
I wont try to explain the reason but there is one:-)


  VoG II 17:57 17 Jan 2007

Select Column A, Format > Cells, click on Date in the left hand pane, click on 14.3.01 in the right hand pane and click OK.

  VoG II 17:59 17 Jan 2007

Ah no, you want the full year:

Select Column A, Format > Cells, click on Custom in the left hand pane, in the box enter

and click OK.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

WPA2 hack: How secure is your Wi-Fi?

Add Depth Of Field to a photo using Tilt Shift Blur in Photoshop

iPhone tips & tricks

Les meilleures tablettes 2017