18. TIMESTAMP NON VOLATILE


 PROBLEM:  How to get a non volatile date after filling a cell?


1. Select the column "Batch" of our table and right-click your mouse;
2. Select "Format Cells";
3. On tab "Protection" unselect "Locked";

TAB
4. Insert in the first cell of the column "Batch" the formula: =IF(Q22<>"";R22;NOW());

TAB
5. On "Conditional Formatting" go to "Manage Rules" and then "New Rule";

Select "Use formula..." and insert: =IF($Q22="";1;0)


TAB
6. Click on "Format";
7. Custom;
8. Select the tab "Number";
9. On "Type" insert  " ; " ;

This will hide those cells with no data entry on column "Batch";

TAB
10. Finally, select "File"
11. Down in "Options" and "Formulas" enable iterative calculation.

TAB

    This formula is a nested IF and NOW function that returns the value in the cell R22 if the value in the cell Q22 is not empty. Otherwise, it returns the current date and time.

    Here’s how it works:

  1.  Q22<>"" returns TRUE if the value in the cell Q22 is not empty.

  2.  2. IF(...;R22;NOW()) returns the value in the cell R22 if the value in the cell Q22 is not empty. Otherwise, it returns the current date and time.