[Excel] automatic date without updating

Hello,

This script is very good, but I tested it and it still doesn't work very well because every time I return to the cell, it updates itself.

I am in the process of creating an incident report document.
In the first part, I use the date to generate a number, that is to say:
Ex: 14/02/2007 15:00
In my cell, I would like to have this 20070214-1500.
For this, I used the classic formula in Excel: =NOW()

What I am trying to do with this cell is to create a unique number when the file is first opened and that it no longer updates, so it should be static. Especially it should be impossible to change the number.

In the second part, I have this:
......| A | B | C | D
13 | No | Date | Time | Participant
14 | 1 | 14/02/2007 | 14:26 | Toto
15 | 2 | 14/02/2007 | 14:26 | Titi
16 | 3 | 14/02/2007 | 14:26 | Tutu

What I would like to do is:
- As soon as a name is entered in column E (Participant)
- The date and time will update automatically but only once, with no possibility to change, unless the name is removed.

What I did: in the Date column, I put this formula: =IF(ISBLANK(D14),"",TODAY())
in the Time column, I put this formula: =IF(ISBLANK(D14),"",NOW())

It should be noted that these two formulas are not functional at all. The cells update automatically every time I enter a new user and also update those of other users. And also, at the same time, the incident number that is based on the date.

I am not someone who masters this tool at all, but I am trying to do it to simplify my daily life.

Thank you to the person who can help me find a solution to my problem.

Hello JvDo,

I tested your VBA program on a blank sheet and it works very well for the application I want to set up.
However, for me the relevant cells are not in columns A and B.
For me, column A is in B and column B is in F!!!
What terms do I need to change in the VBA programming to make it work?
Best regards

I am going to create a program to monitor expiration dates of products at my disposal.
For example, I have product A that I received three times from different suppliers who obviously have different production and expiration dates. I need help creating a file for calculating the average expiration date detection.
Thank you for your help, JvDo.

Best regards.

Thank you, that really helped me!!!!!!!!!!! :)

Hello, it works, thank you. However, I have a small request for the sake of perfection.
When I enter a character in column B (2), the date displays correctly in column F (6).
However, would it be possible to modify the macro so that when I remove the character in column B, the date in column F disappears???
Thank you in advance
Best regards

Good evening,
Try this code which is more complete as it manages the deletion of a multi-area selection.
It works for me, but I haven't tried to test everything.

Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False Set vtarget = Intersect(Target, Columns(2)) If Not (vtarget Is Nothing) Then For Each varea In vtarget For Each vcell In varea If Not (IsEmpty(vcell.Value)) Then Range("F" & vcell.Row).Value = Now Else Range("F" & vcell.Row).ClearContents End If Next Next End If Application.EnableEvents = True End Sub

Kind regards

Great,
That is very useful thank you very much,

Personally, I have one last question to make my file top-notch,

What should I add for the macro to work only from row N?

Hello
just replace the column number (N) you want in place of (2)
in the line:
Set vtarget = Intersect(Target, Columns(2))
and of course replace ("F") with the name of the column where you want to display the result;
best regards

Thank you,
But that's not the problem, that works it's just that I want this formatting to apply only from row N (16 in this case).
Otherwise, when I modify my header, I get dates popping up everywhere :-/

I think I'll add an If to check if the row is greater than 16, then the rest of the macro.

But I'm missing the syntax...