User Profile
kovesp
Copper Contributor
Joined Sep 04, 2021
User Widgets
Recent Discussions
Re: Automatically adding the date/time to a cell when another cell is updated
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.16KViews0likes0CommentsRe: Automatically adding the date/time to a cell when another cell is updated
=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.16KViews1like9CommentsRe: Automatically adding the date/time to a cell when another cell is updated
See my reply below, but use this instead of the first version: =IF(COUNTA(C1:Z1)=0," ",NOW()). Adjust for however many columns you want to cover in the row. Sorry replied to NikolinoDE instead of Carly_Forrester.238KViews0likes0CommentsRe: Automatically adding the date/time to a cell when another cell is updated
No VBA needed; and that would not work when the workbook is opened in the browser or mobile platforms. If the column where you want to automatically set the current date is say B, the column where the data is being entered is say C, and you are starting from B1 then therre are two variants: If you want the date to update whenever the data is changed in column C, then place =IF(ISBLANK(C1)," ",NOW()) in B1 and copy down. If you want the date to be set only when the data in column C is changed from blank to a non-blank value (but not when the value is changed from non-black value to another non-blank value) then place =IF(ISBLANK(C1)," ",IF(ISBLANK(B1)," ",NOW())) in B1 and copy down. These work when the workbook is opened from Windows or Android (so it probably also works on IOS, but I don't use that platform, so can't verify). Note: There was a previous reply which was similar to the first option above, but used TODAY(). That won't work, because the TODAY() function updates whenever the workbook is opened. NOW() will not do that, it "freezes" the date when invoked.238KViews0likes8Comments
Recent Blog Articles
No content to show