# How do I auto-enter date/time when adjacent cell is populated?

I am new to LibreOffice Calc. I need to use LibreOffice Basic but do not know the code to autoenter date/time when an adjacent cell is populated.

For example, whenever Column A, B and/or C is populated, I would like Column D to automatically enter the date and time.

Thank you

Edited 6/7/16 Hi again I think the formula I have might need some "tweaking". I copied the above formula for all the rows as follows: For Cell D1, I have =IF((A1<>"") OR (B1<>"") OR (C1<>""),NOW(),"") Then for Cell D2, I have =IF((A2<>"") OR (B2<>"") OR (C2<>""),NOW(),"") And for Cell D3, I have =IF((A3<>"") OR (B3<>"") OR (C3<>""),NOW(),"")

So when data is entered into either A1, B1 or C1, the current date/time auto-fill D1.

However, after a few minutes, when data is entered into either A2, B2 or C2, the current date/time auto-fill D2 - but it also changes the date/time in D1.

And when I close the file, and open it again, all the dates change to current date/time.

I need to date/time to stay as it is once data is entered in each row without changing.

Thanks Lisa

edit retag close merge delete

Unfortunately at the time I didn't foresee the potential repercussions. I am also stumped as to an easy resolution. I can comprehend a user defined macro but that can be messy. I will post if something comes to me or hopefully someone else has a solution.

( 2016-06-08 02:37:47 +0100 )edit

As an alternative, you can enter the current date/time in Dx by pressing Ctl+Shift+;. This will not change as it is not a function. The function NOW() is re-calculated on all sheet changes.

( 2016-06-08 03:13:06 +0100 )edit

Thank you for all your help. I found a solution here:

The only difference is only ONE adjacent cell is populated (and not three) for the fourth cell to autofill date/time, but I can live with that for now.

Thanks again

( 2016-06-08 05:45:12 +0100 )edit

Sort by » oldest newest most voted

Place this in D1 =IF((A1<>"") OR (B1<>"") OR (C1<>""),NOW(),"") and it checks for anything in A1, B1 or C1 (even a space) and if anything is present in any of those cells D1 will display the current date/time. Make sure D1 is formatted for the date/time display you want. Also insure the column is wide enough to display the Date/Time otherwise you will see '####'. If nothing is present in ALL three cells, D1 will be blank. "" = NULL - nothing. <> is unequal.

Edited 6/8/16 - Thank you for the link and what I was missing. Here is your statement to check all three columns:=IF((A1="")AND(B1="")AND(C1=""),"",IF(D1="",NOW(),D1)). This is for col D where col A, B or C has an entry. Iterations must be turned on Tools->Options and under LibreOffice Calc->Calculate check box for Iterations. Column D must be protected and other columns you use for entry should not be protected Format->Cells->Cell Protection. Finally Tools->Protect Sheet.

Please note: Date/Time will not change once an entry is made in Col A, B, or C. However if all three are deleted, the Date/time will disappear and any new entry in A, B, or C will place a new Date/time in column D.

more

Thank you!

( 2016-05-28 02:46:36 +0100 )edit

Be aware of some facts, please:
1. This will not work as intended if the date-time column is hidden.
2. The working of such formulae depends on the settings for iterative calculations.
3. Iteration is only specified for numeric applications. Its applicability for purposes as discussed here is unspecified and subject to changes without notification.
4. No corrections by Ctrl+Z!
I experimented a lot in this field and would no longer recommend to rely on formulae of the above given kind

( 2016-06-08 21:26:38 +0100 )edit

Thank you for this info @Lupp.

( 2016-06-08 21:41:30 +0100 )edit