Sep 30 2018 05:46 PM
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?
Oct 21 2021 08:03 AM
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.
Jan 18 2022 02:03 PM - edited Jan 18 2022 02:25 PM
@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.
Jan 19 2022 05:51 AM - edited Jan 19 2022 05:53 AM
=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 ...
When I added this macro to the workbook
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.
Jan 19 2022 07:07 AM - edited Jan 19 2022 07:08 AM
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. :)
Jan 19 2022 07:11 AM
Jan 19 2022 07:25 AM
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
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.
Jan 19 2022 08:54 AM - edited Jan 19 2022 09:13 AM
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.
Feb 28 2022 08:47 AM
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
Mar 30 2022 07:59 AM - edited Mar 31 2022 08:04 AM
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.
May 07 2022 11:44 AM
May 07 2022 12:08 PM
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)
May 07 2022 12:15 PM
So my question to add to this is , will this automatically update every wed?@Hans Vogelaar
May 07 2022 12:37 PM
May 07 2022 12:43 PM
Jun 21 2022 08:31 AM
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
Jun 21 2022 10:17 AM
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.
Sep 13 2022 11:20 AM
Sep 20 2022 02:16 AM
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?
Sep 20 2022 07:32 AM
@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.
Oct 29 2022 08:44 AM
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