Automatically adding the date/time to a cell when another cell is updated

[Joseph Huisman]

I am trying to create a spreadsheet where when a specific cell is updated in anyway, the date/time stamp automatically updates in the cell right beneath the cell that was updated. I only need to have a one to one relationship with multiple cells. IOW, I have 5 cells in a row going across. When any one of the 5 cells is updated, I want an updated date/time stamp to appear in the cell directly below the cell that was updated. Is there a way to do this? 

[#2]

@zoyra 

Try this formula "=IF(A1-O1="","",NOW())" 

I placed it in Cell P1 of the document I have it in. So when I make any changes in Cells A1 through O1, Cell P1 will change with the date and time the change was made.

I'm not sure how well it will work if you place it in a middle cell of the formula. I hope this helps.

[#3]

@Haytham Amairah 
Any other good sources using VBA, this thread looks like it ended up saying the opposite of you and died. But this is exactly what I need. I could use some help on what to search for to help find what I need.
Thanks.

[#4]

=IF(ISBLANK(C1)," ",IF(ISBLANK(B1)," ",NOW()))

The current date is to be automatically inserted into B1 when C1 becomes non-blank.

For this to work, one needs to set Enable iterative calculation on the Formulas page of Options.

However ... 

• This used to work in the Android version of Excel when using the smae workbook, but Microsoft, in their wisdom, "fixed"it at some point and there is no option for enabling iterative calculation in the Android version.
• Worse, when the workbook is then opened in Windows, the option is turned off, hence the formula stops working. 
• The option remains set, as long as the workbook is not touched on Android.

When I added this macro to the workbook

Private Sub Workbook_Open()

   Application.Iteration = True

End Sub

and saved it as XLSM (as required) it started working again. It now also works on Android, despite showing a message that the Android version doesn't support VBA.

Ah, the mysterious ways of Microsoft ... 

Anyway ... thanks for asking the question: I didn't think of trying the above before now.

[#5]

@kovesp 

This worked... But!  

I got a negative value returned and no matter the formatting it would not reflect a date.

When I entered text into the block it returned the following value: -44580.41589

So, further troubleshooting shows when it is NOT a negative number it is the value of the time I entered the data into the block.

So how to modify the formula so it DOES NOT return a negative value could resolve this....

Hunting I will go. 

I am very close, thank you. :)

[#6]

@kovesp 

Got it, I think!

Testing now...

Simply placed a - in front of NOW in the formula... 

[#7]

@kovesp 

Welp, as suspected, this doesn't work as intended.

VERY VERY close though.

The reason is because upon saving the file, everything updates again to show the current time.

So to really close this formula down, I think the best way forward is to

1. Create a live reference block on the sheet that reflects the current date/time
2. THEN update this formula to grab the present VALUE from there.

My other thought would be to have the formula self-destruct upon first use, but that would be VERY unlikely.   But an idea for a future version.  Like "Convert to value after first use".

@Haytham Amairah  Tagging you since this might be a good idea to add vs VBA.  I see your actively suggesting.

Thanks all.

[#8]

OK, the problem was that I tried to optimize the formula at some point. This is what works:

=IF(C1<>"",IF(B1="",NOW(),B1),"")

I verified that once the date is set, it does not change after the file is saved and then reopened (I set the display format to yyyy-mm-dd HH:mm:ss to verify that the current value of NOW() is used only when C1 is set to non-blank).

The only time it is updated is when its buddy (C1 in this case) is set to blank and then to non-blank.

I don't understand why you would get negative values from NOW(), that isn't normal. Negating it is not a good idea, because the root cause is somewhere else.

[#9]

This works - BUT - it changes the entire column of dates to todays' date - stupid excel - I ONLY want the cell on the same line as the quantity that was changed - to change

[#10]

Hi, I am using your formula above =IF(ISBLANK(C1)," ",NOW()) which is working as intended in 1 of my tabs. In my case I have 5 different tabs in a workbook and the time changes regardless of which cell changes on any tab. Is there a way to only update the time when a cell on only 1 tab changes? I'm also noticing that if I change a different workbook, the update time also changes in the other workbook, I don't want that to happen. Hope there is a way to do this with a formula.

[#11]

Not sure if anyone can help me out with this , but im sure this will need to be input through VBA , im looking to make 1 cell update the date ONLY every 7th day for example , i have a spread sheet that i have to input personnel info as the come to and leave our facility , and at the top of this spread sheet are a series of cells that read **IN THIS ORDER** "Arrivals (Wed-Tues)" "5/4/22" "THROUGH" "5/10/22" so where "5/4/22" is i am looking to have this particular cell update ESSENTIALLY every wed , IE i need 5/4/22 to update to 5/11/22 , i already have the formula figured out for the 5/10/22 part that is easy such and such +6 and you have the 7th day of your week , i have searched high and low for an existing VBA code to create this but have had no luck thus far , can anyone help me?

[#12]

@Sloany2385 

The formula =TODAY()+1-WEEKDAY(TODAY(),13) will return the last Wednesday on or before today.

If you want the Wednesday after that, use =TODAY()+8-WEEKDAY(TODAY(),13)

[#13]

So my question to add to this is , will this automatically update every wed?@Hans Vogelaar 

[#14]

@Sloany2385 

Yes, it will:

[#15]

thank you very much your a life saver!!!

[#16]

@Dave_Ramsay 

Hi,

I am not sure why it's not working while adding function.

On my current excel version. It doesn't allow the = for function.

It give me the following message

That excel consider = as a formula so to use like 1+1 the cell display 2.

Any idee why it doesn't accept above formule =if(c2.....)

When writing in the cell

[#17]

@Marc-1210 

Perhaps in your locale you shall use semicolon as separator instead of comma, i.e.

=IF( c2; 1; 2)

not

=IF( c2, 1, 2)

or opposite. 

[#18]

Hi @Haytham Amairah,
I am using this vba snippet on a shared worksheet that I use with my helpdesk team. If I type information into a cell, the date appears. If one of my teammates who shares the document, types in that cell, the date field appears blank. How can I fix this?

[#19]

@kovesp 

many thanks for your formula, it works for me, just the problem is that if I update lets say C1, it does update the date/time into all lines & not only B1 as I was expecting. What is my mistake?

[#20]

@Sergei Baklan 

Yes you are right, after a while, I notice I needed to use the "opposite" since at work, we are using the french version of microsoft office.

[#21]

@kovesp

Hi I have the same issue
my spreadsheet has steps and when a step is changed or edited another cell should reflect the date
and since i have many steps I need to see many dates to know when each step was edited

for example:
cells with content to be edited are :
J8, J14, J23,P8,P14,P23

and cells that should reflect changes date are:
I11,I17,I26,O11,O17,O26

So cell I11 should show the date when J8 is edited
(whenever J8 is edited, I11 should update the date)
and whenever J14 is edited, I17 should update the date (show the date),
and so on

I tried =IF(J8="","",NOW())
and I tried =IF(J14="",IF(I17="",NOW(),I17),NOW())
and tried =IF(ISBLANK(P14)," ",NOW())
and tried =IF("$P8"="","",NOW())
and =IF(P8<>"",IF(O11="",NOW(),O11),"")
all of them are working but with 2 problems:
- if i edit any cell in the sheet, the date is updated
- but it is updated in all the cells that contains this formula despite that the formula in each one has different cell number as referred above

any help is appreciated
thank you in advance