Aotomatically dating a column in Excel

  Ray5776 19:49 16 Jan 07
Locked

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.

Thanks

  Ray5776 19:52 16 Jan 07

And where to get spelling lessons, sorry I meant Automatic

  VoG II 20:06 16 Jan 07

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 07

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

Ray

  VoG II 20:55 16 Jan 07

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

  Ray5776 22:20 16 Jan 07

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

ray

  Simsy 23:54 16 Jan 07

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.

Regards,

Simsy

  Simsy 00:12 17 Jan 07

is a shot of what it looks like...

click here

Hope it helps,

Regards,

Simsy

  Ray5776 17:54 17 Jan 07

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:-)

Ray

  VoG II 17:57 17 Jan 07

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 07

Ah no, you want the full year:

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

dd.mm.yyyy

and click OK.

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

Nintendo Switch review: Hands-on with the intuitive modular console and its disappointing games…

1995-2015: How technology has changed the world in 20 years

This abstract video touches on division in our technologic world

Best alternatives to iTunes for Mac | Best music players for macOS: Free your music from the…