Set todays date in a cell but then fix it

Is it possible to get today’s date entered automatically into a cell, but then to fix it so that the entered date remains unchanged. I have tried the today() function, but the date changes each time I reopen the spreadsheet. I’m looking to see if there’s a quicker method than having to enter the date manually each time.

Hi

You can use the Ctrl+; shortcut to enter the date.

@Lupp also proposed a smart use of circular references here (question was tagged “writer” but it is a Calc solution).

Regards

But the Ctrl+; shortcut enters only the current time not date… :frowning:

@gianfrus : use Shift+Ctrl+; and extended format…

  1. Make sure “Iterations” are enabled:

Go to Calc. Then (in the menu) go to ‘Tools’ → ‘Options’ → ‘LibreOffice Calc’ → ‘Calculate’ and make sure the ‘Iterations’ checkbox is enabled.

  1. If the fixed date should be in cell A1 …

Right click cell A1 and choose ‘Format Cells’. On the tab ‘Numbers’ in ‘Category’ choose ‘Date’ and click the ‘OK’ button.

Now enter this formula in cell A1:

=IF(A1="";TODAY();A1)

Current date will appear in cell A1 and is now fixed :slight_smile:

Of course you can extend this formula, for example:

“enter the current date ONLY when a value is entered in cell B1”:

=IF(A1="";IF(B1="";"";TODAY());A1)

Now the current fixed date will only appear in cell A1 if you type something in cell B1.

Enjoy :wink:

The Question was exactly: insert Todays-Date into Cell and fix that date no more no less – so do it as @PYS has already suggested or choose some other possible shortcut – no need for this kind of oversophisticated formulas

@karolus

No that was not the question. The question was, I quote, “Is it possible to get today’s date entered automatically”. That indicates the use of a formula and not manual entering!

great stuff @LibreGuy how can I edit this to change row by row eg =IF(A2="";IF(B2="";"";TODAY());A2) for a large number of rows without editing it each time manually? If its possible i’d love to know!

Can this be altered to include the time?

@pherriot: replace TODAY with NOW

Thanks for all the replies, but just using “Ctrl ;” seems to do what I wanted it to do.

Why was this “answer” upvoted? @PeteofEbor: This should be a comment under an answer, not a separate answer. Also, it sounds like @PYS’s answer is what you wanted, so please mark it as correct. See guidelines for asking.

Unfortunately this does not work anymore in Libreoffice 6. It saves the default date which will not change anymore.
A very handy code, which I use a lot, but I do not know how to fix it.

What doesn’t work? which code? what steps exactly you do, how it worked before, and what happens now? Please be specific, otherwise, it doesn’t make sense.

It’s probably related to the keyboard. I didn’t report it because I don’t use this kind of shortcuts except when a forum request requires it, but already for quite a time the standard shortcut Ctrl+Shift+; didn’t work with my German keyboard where the semicolon is Shift+,
On the other hand I tried today also a differently assigned shortcut (Ctrl+q) and it worked.

(Back to this old thread due to an accidental bump.)

“Just for fun” (not exactly) I combined some pieces of Basic code an polished the result a bit.
Now I can demonstrate how to do this (inserting a fix date), related (fix DateTime, fixTime), and also rather different things by user code called from buttons (formcontrols).

The code contained in the demo is focussing on the evaluation of the calling event thrown by a click, and in specific how to pass parameters to routines called by control events.

I don’t expect lots of real-world-applications since documents containing control elements for such purposes should be exceptions, not the rule. The passing of parameters via a property of the source of an event is unfortunately not available for means the UI offers for calling custom code.

Have your fun!

I have the same problem - spent days trying to solve why the neat little code =IF(A1="",TODAY(),A1) for autoentering the date and then fixing it does not work anymore. This has totally spoiled the functioning of a very useful spreadsheet table of mine. Can anyone help? Romjon.

Same here. It may have worked before but it only displays a date like: “1899-12-30 00:00:00” now…

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 autodate a given cell within the same row whenever this occurs. We attach this sub/macro to the sheet event “Contents changed”.

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 Sub

You may consider to also read my comment on “the other topic”: Calc automatic date on data entry - #6 by paddywan.