Forum Discussion
Automatically adding the date/time to a cell when another cell is updated
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.
=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
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.
- 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?
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)
So my question to add to this is , will this automatically update every wed?HansVogelaar
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. 🙂
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
- Create a live reference block on the sheet that reflects the current date/time
- 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.
