Excel - non volatile date

  montyburns 07:52 26 Jul 2006

Hi folks

Trying to set up a formula to automatically insert a date into a cell when another cell has data inserted, along the lines of


This works fine. If B3 is empty, so is the cell with the formula, but if a value is typed into B3, the formula cell displays today's date

Problem is, when I open the document tomorrow, it'll display tomorrow's date!

Is there any function which does the same thing, but "stamps" a non changing date?

(I guess I could use a macro, but trying to keep things simple!)



  Noldi 08:31 26 Jul 2006

The only way I can get your Idea to work adding a date only when B3 has data in is to put TODAY in cell A1 then use fomula.
As for saving the date I have to look at that, but there are people on this forum that can answer this better than me.

  VoG II 08:32 26 Jul 2006

Function Mydate(r As Range)
If r.Value = "" Then
Mydate = ""
Mydate = Date
End If
End Function

To use it


and format the result cell as Date.

  Noldi 08:47 26 Jul 2006

click here

  montyburns 17:56 26 Jul 2006

Must admit that I have been using the CTRL; shortcut.

In case you think I'm just too lazy to type the date in (!) the data to be inputted goes in quite a few columns, and I'm just thinking it would save a little time if one of these columns would trigger the date.

Seems odd that there isn't a function, after all, IIRC, Word has a similar thing (so you can have a field in a letter which gives the date it's produced OR a constantly updating one for standard letters)

Looks like a macro it is then! Ta VoG!

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

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

What went wrong at the Designs of the Year 2017

iPhone X news: Release date, price, new features & specs

Comment regarder des séries et talk-shows américains en France ?