1

I am working with Excel Online (browser) because I am working with more people. I am trying to create a time stamp in a cell (eg. B1) after I write the value in a diferent cell (eg A1). In the Excel app I could do this with VBA using the Now() command and I would get the Value. for example in Cell B1, after I would write a value in A1 cell.

As Excel online doesn't support VBA I can still user Now() command by programming the Cell I want, with something like this (B1):

=IF(A1<>"";NOW());"")

I wonder, is there a better way to do this in Excel Online?

toraritte
  • 6,300
  • 3
  • 46
  • 67
fr0zt
  • 733
  • 4
  • 12
  • 30
  • 1
    Please note that this will not be a timestamp but a simple display of the current time and date (depending on your formatting), which will continue to update as time passes. – riskypenguin Oct 02 '19 at 15:34
  • I understand. So what's the better way to create a time stamp in B1 Cell whenever I insert a value in A1 cell? – fr0zt Oct 02 '19 at 15:36
  • I try to avoid working with Excel Online, so sadly I don't know. – riskypenguin Oct 02 '19 at 15:37

2 Answers2

0

I followed this article - "Circular References Trick to Automatically Insert Date and Timestamp in Excel", and it also seems to work in Excel Online.

First of all, you need to go to File > Spreadsheet Settings > Calculation and switch Iterative Calculation to ON.

In column B you need to use the following formula which normally throws a Circular Reference error:

=IF(A2<>"",IF(B2<>"",B2,NOW()),"")

Thanks to Iterative Calculation being turned on, NOW() function won't re-calculate every time something changes in the sheet.

enter image description here

However, note that it doesn't work for updating the values. For example, if you want to change the value in cell A2 your Timestamp won't update. You would need to delete A2's value and then update it with the new value.

Justyna MK
  • 3,523
  • 3
  • 11
  • 25
  • I've read about that. Thanks. Can you tell me if it's there a way to store the value of the date? This is, if I delete the A1 cell it will all go away... But it's there a way to store the value of the date after the first insertion in the A1 cell? – fr0zt Oct 03 '19 at 08:41
  • Unfortunately not, sorry. As you said, the formula in B2 will automatically return blank once you delete value from cell A2. – Justyna MK Oct 04 '19 at 09:22
  • Edit - maybe consider using some other syntax, like "-" / 0 / #N/A, instead of deleting the data. In such case Timestamp won't disappear and your Data Entry won't disturb your calculations. – Justyna MK Oct 04 '19 at 11:20
  • I see. The thing is: If I could use macros witb vba It would insert the value of the date in the cell. But by programming the cell I will have a dynamic value on it. – fr0zt Oct 04 '19 at 13:52
0

Paste Values: CTRL+SHIFT+V

When one clicks on the clipboard icon above "Paste" will show a help pop-up:

Cut = CTRL + X
Copy = CTRL + C
Paste = CTRL + V
Paste Values: CTRL+SHIFT+V

Workaround

  1. Insert =NOW() into a cell
  2. Copy it (e.g., with CTRL+C)
  3. Paste it by value wherever (i.e., click on Home > Paste > Paste Values)
toraritte
  • 6,300
  • 3
  • 46
  • 67