Ask Your Question

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

asked 2016-05-27 12:40:50 +0100

Skyeblue gravatar image

updated 2016-06-07 23:49:31 +0100

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.

Can you help again please?

Thanks Lisa

edit retag flag offensive 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.

Ratslinger gravatar imageRatslinger ( 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.

Ratslinger gravatar imageRatslinger ( 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

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

1 Answer

Sort by » oldest newest most voted

answered 2016-05-27 14:38:15 +0100

Ratslinger gravatar image

updated 2016-06-08 18:30:17 +0100

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.

edit flag offensive delete link more


Thank you!

Skyeblue gravatar imageSkyeblue ( 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

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

Thank you for this info @Lupp.

Ratslinger gravatar imageRatslinger ( 2016-06-08 21:41:30 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-05-27 12:40:50 +0100

Seen: 2,906 times

Last updated: Jun 08 '16