Hello all,
Is it possible to automatically have a date entered in a cell when data in another cell is entered? Example: column A is awaiting data. If data is entered in say A4, I would like cell B4 to automatically enter the date of the data entry of cell A4, otherwise it would stay blank. Hope I was clear. Thanks in advance.
From the beginning it wasn’t specified whether a once created datestamp should be fix “forever”, or should be renewed if the cell it is associated with is edited.
This is a question posted in slightly different ways again and again. I will noenetheless try a new answer.
To do this based on formulas would require to permit “Iterations” (circular references) and to misuse them for purposes they are not made for. Accepting some grave complcations we can get a very fragile solution for the task this way. In specific an error changing or deleting a date-time-stamp inadvertently cannot be made undone (by Ctrl+Z e.g.)
To do it with the help of custom code (“macros”) is against the grain regarding what spreadsheets essentially are made for. It is, however, possible and much less fragile / more stable.
See this demo.
===EDIT1 2019-12-27 about 21:35UTC===
Completely abandon attempts to do it with formulas. No reliable solution possible. See comment below.
===End EDIT1===
There is this slightly reworked demo.
About 2 years later I come back to this therad being reminded of it by a different one.
I take the opportunity to state that attempts based on formulas are proven again to be very fragile. The solution I gave in the above linked old demo still is working in LibO V 5.4.4.2, but does no longer work as expected in V 6.3.3.2. I will not again try to find a “fix” or a workaround. Trying to do it this way simply means wasting time.
On the other hand I can confirm that the way to pass parameters to Sub via the name of a CellStyle proved suitable in this case and in some more since.
After scouring the internet for the solution in libreoffice / openoffice I was unable to find the correct snippet I needed. I became annoyed so I did a little bit of research and knocked this up. I hope someone else finds this useful.
The function is called AutoDate, and its intention is to watch over a column for changes, and to automatically insert a formatted date into a given cell/column or neighbour within the same row whenever this occurs. We attach this sub/macro to the sheet event “Contents changed” via “Sheets->Sheet Events”
Sub Autodate(e)
dim adjacentCel as Object
ODoc = ThisComponent
OSheet = ODoc.CurrentController.ActiveSheet
dim WatchedColumn as Integer
dim AutoDateColumn as Integer
WatchedColumn = 1 REM This is the column which we will detect changes within.
AutoDateColumn = -1 REM This column is a relative reference to the watched column. Negatives are too the left, and positives too the right.
REM using a combination of (1,-1) will watch column B for changes, and will autodate the corresponding cell in column A
If e.supportsService _
("com.sun.star.sheet.SheetCell") Then
dim adjY as Integer
If (e.CellAddress.Column = WatchedColumn) Then
adjY = e.CellAddress.Column + AutoDateColumn
adjacentCel = OSheet.getCellByPosition(adjY, e.CellAddress.Row)
adjacentCel.String = Format(Date(), "d/m/yyyy")
adjacentCel.HoriJustify = com.sun.star.table.CellHoriJustify.RIGHT
End If
End If
End SubThe handler for onContentChanged is also called if
- (1) contents were deleted or
- (2) contents were inserted by some variant of pasting.
- (3) contents were edited via the focus cell for a range, quitting by
Alt+Enter.
In case of (1) the event can be (support the respective relevant service) a SheetCell, a SheetCellRange or a SheetCellRanges (Pl.!) object. In case of (2) or (3) SheetCellRanges can’t occur.
However, your reduction to the SheetCell case may lead to inconsistent (misleading) results.
===
Be sure to note:
If an action of deleting or editing afflicts more than one sheet at the same time (multiselection via tabs), each one of these sheets will call its handler (if any) for onContentChanged independently, and pass to it as the only parameter the respective “set of cells” restricted to this single sheet.
===
Also:
- Default date formats should always be ISO 8601 (
YYYY-MM-DD). In specific “slashed formats” are mostly ambiguous if not completed by explicit information about the locale they’re based on. - Explicit horizontal alignment should be avoided. Keeping
Standardalignment uinveils the relevant information concerning the result/content type to the user (if not the column is too narrow. In your case a user expecting this behaviour would be mislead to assume the result is a number, while it actually is text.
You may try a revision inserting the fix date as a number (standard representation in spreadsheets -sigh), assigning the Key of the wanted format for the display to adjacentCel.NumberFormat. How you can get (or create if needed) the appropriate key you find described in Andrew Pitonyak’s famous texts.
===
The action to be taken for any cell or cell range may be chosen based on the name of a dedicated cell style. If an afflicted range not has a common style , you can continue using its property .CellFormatRanges
Some countries still do not use the Metric system. 
Yes, by firing this formula, calc can automatically display the cell value contents in another cell.
Column: A1
Entry Value: =today()
Column B4
Formula: =IF(ISBLANK(A1),"",A1)
Hope this helps!
I’m not currently using a database of my own design.
However, the question reminds me of some developments concerning databases I read of.
May this be the web search you (the questioner or a visitor to the topic) should do?