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

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.

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.

Hi Joseph,

There is no function in VBA called Today, the VBA function equivalent to the Worksheet function https://support.office.com/en-us/article/TODAY-function-5EB3078D-A82C-4736-8930-2F51A028FDD9 is https://docs.microsoft.com/en-us/office/vba/Language/Reference/User-Interface-Help/date-function.

So you have to use Date instead of Today as follows:

Sub Worksheet_Change(ByVal Target As Range)

 If Not Intersect(Target, Range("A1,B1,C1,D1,E1")) Is Nothing Then
    Target.Offset(1, 0) = Date
 End If

End Sub

After you apply this change, you may get the date in the worksheet like this:

Where time appears as zeros!

If you encounter it, don't worry, it's just a format, and you can change it to a short date from the Home tab as follow:

Hope that helps