[Calc,Python,Basic,Base] Several ways to time stamps

[Villeroy]

Edit: 2009-03-28: How to insert a time stamp into Calc without any macro

Type =NOW() without entering the formula. Hit F9 to calculate the formula right on the input line. Then hit Enter twice (confirm and enter).
Using a shortcut to NOW() (a tiny little bit shorter):
Call menu:Insert>Names>Define...[Ctrl+F3]
Name: n
Refers to: NOW()
Hit buttons [Add], [OK]
Type into a cell: =n F9,Enter,Enter

NOW() returns the full date and time info as a formatted number. Having such a full stamp in a A1, =INT(A1) returns the day-portion, MOD(A1;1) returns the time-fraction only. You can use number formats to display only the date or the time without changing the value.

Edit: 2010-12-29: How to insert a time stamp depending on another cell value into Calc without any macro

menu:Tools>Options...OOoCalc>Calculate: Enable iteration with 1 step

Code: Select all

Cell B1: =IF(A1="";"";IF(B1="";NOW();B1))

returns the current time when A1 is not empty nor empty string.
Thanks to this forum's most valued member Robert Tucker pointing us to http://www.mcgimpsey.com/excel/timestamp.html

Several macros to write time stamps into spreadsheet cells. It writes naked numeric values, leaving all formatting up to you. Straight ahead, no gimmicks, no error-handling. Just tested against the assumed scenarios.

1. NowToActiveCell
Writes the time stamp to the currently active input cell. Best used with a shortcut.

2a. NowToTimeStampCell
Writes the time stamp to a named cell "TimeStamp" anywhere in the current document.
2b. NowToEmptyTimeStampCell
Same as above, leaving alone an already filled stamp-cell.

3.a NowToColumnAByValidation
Triggered by a failed validation. Writes the time stamp to the same row's column "A" when a validated cell has been modified. Set validation to "Text Length = 0" with action set to "Macro". The validation fails, when you put something into the cell (length becomes >0), which triggers the macro.
3b. NowToEmptyColumnAByValidation
Same as above, leaving alone an already filled stamp-cell.

Python Install
Click the install button in the attached document. See edit notes of 2021-07-21.

Basic Install
Re: [Tutorial] How to install a code snippet

Edit: 2008-12-21 Replaced Python attachment. It can be called from a menu or button now.

Edit: 2010-09-24: Simplified the Python module, added NowToRightNeigbour

Basic version:

Code: Select all

REM  *****  BASIC  *****
REM I won't update this Basic code. You may refer to the latest Python
Sub test_splitStringAddress()
REM instead of splitting the string address you may simply use the active cell,
REM ignoring the strings from the validation trigger. See updated Python code
Dim s$,sSh$,sRg$
	a=Array("'my sheet'.A1", _
	"$'my sheet'.A", _
	"'file:///doc.ods'#'my sheet'.A1", _
	"$'file:///doc.ods'#$'my sheet'.A")
	for i= 0 to uBound(a())
		s = a(i)
		splitStringAddress(s,sSh,sRg)
		print sSh,sRg
	next
End Sub
sub splitStringAddress(s$, sSheet$, sRange$)
dim sURL$, linkPos%, sheetPos%
	' remove leading $ in any case:
	if left(s, 1) = "$" then s = mid(s, 2)
	linkPos = instr(s, "'#")
	if linkPos > 0 then
		sheetPos = instr(linkPos, s, ".")
		sURL = left(s, linkPos +1)
		sSheet = mid(s, linkPos +2, sheetPos -linkPos -2)
		sRange = mid (s, sheetPos +1)
	else
		sheetPos = instr(s, ".")
		if sheetPos > 0 then
			sSheet = left(s, sheetPos -1)
			sRange = mid (s, sheetPos +1)
		else
		sRange = s
		endif
	endif
	if left(sSheet, 1) = "$" then sSheet = mid(sSheet, 2)
	if (left(sSheet, 1) = "'") and (right(sSheet, 1) = "'") then
		sSheet = mid(sSheet, 2, len(sSheet) -2)
	endif
	sSheet = sURL & sSheet
end sub
'-------usefull helper-function, returning focussed cell 
'by UROS > http://www.oooforum.org/forum/viewtopic.phtml?t=19348
REM 2006-08-09: fixed error when row > 8191
'		 ;sh;							;lSheet +3
'100/60/0;1;tw:309;2/2/0/0/0/0/2/0/0/0/0;253/8191/0/0/0/0/2/246/0/0/8158;0/0/0/0/0/0/2/0/0/0/0
'100/60/0;1;tw:309;2/2/0/0/0/0/2/0/0/0/0;253+8192+0+0+0+0+2+246+0+0+8158;0/0/0/0/0/0/2/0/0/0/0
Function getActiveCell(oView)
Dim as1(), lSheet&,lCol&,lRow$, sDum as String,bErr as Boolean
	as1()  = Split(oView.ViewData, ";")
	lSheet = CLng(as1(1))
	sDum = as1(lSheet +3)
	as1() = Split(sDum, "/")
	on error goto errSlash
		lCol = CLng(as1(0))
		lRow = CLng(as1(1))
	on error goto 0
	getActiveCell = oView.Model.getSheets.getByIndex(lSheet).getcellByPosition(lCol,lRow)
exit Function
errSlash:
	if NOT(bErr) then
		bErr = True
		as1() = Split(sDum, "+")
		resume
	endif
End Function
Sub printStamp(oCell)
    '''Put the current time into the active cell.
    'No formatting intended. Apply any date/time formatting you like.'''
    oCell.setFormula("=NOW()")
    oCell.setValue(oCell.getValue())
End Sub

Sub _NowToColumnAByValidation(sAddr, bOverwrite)
Dim s$,r$
    iCol = 0
    doc = thisComponent
    splitStringAddress(sAddr,s,r)
    sh = doc.Sheets.getByName(s)
    rg = sh.getCellRangeByName(r)
    iRow = rg.RangeAddress.StartRow
    c = sh.getCellByPosition(iCol, iRow)
    if bOverwrite or (len(c.getFormula())=0)then printStamp(c)
End Sub

Sub _NowToTimeStampCell(bOverwrite)
    oNames = thisComponent.NamedRanges
    oName = oNames.getByName("TimeStamp")
    oRg = oName.getReferredCells()
    c = oRg.getCellByPosition(0,0)
    if bOverwrite or (len(c.getFormula())=0)then printStamp(c)
End Sub

Sub NowToEmptyColumnAByValidation(sFormula, sAddr):
    '''Current time to empty column A, triggered by failing validation'''
    _NowToColumnAByValidation(sAddr, False)
End Sub

Sub NowToColumnAByValidation(sFormula, sAddr):
    '''Current time to column A, triggered by failing validation'''
    _NowToColumnAByValidation(sAddr, True)
End Sub

Sub NowToTimeStampCell():
    '''Put current time into a cell named "TimeStamp"'''
    _NowToTimeStampCell(True)
End Sub

Sub NowToEmptyTimeStampCell():
    '''Put current time into an empty cell named "TimeStamp"'''
    _NowToTimeStampCell(False)
End Sub

Sub NowToActiveCell():
    '''Put current time into the currently active input cell'''
    oDoc = thisComponent
    oView = oDoc.getCurrentController()
    oCell = getActiveCell(oView)
    printStamp(oCell)
End Sub

Edit: 2009-03-28: Adding a link to another approach involving c.s.s.util.XModifyListener. Not as easy to implement since the listener needs to be attached to the respective range(s). Trouble with race conditions when used with volatile functions such as NOW(). This is why I suggest the NowToColumnAByValidation with the validation-trick: Re: Run a python macro when cell contents change

Edit: 2016-11-04: Made the code Python3 compatible. Works with recent versions of LibreOffice

Edit: 2021-07-21: Added milliseconds to time stamp. Apply date-time format in case of default format. Wrapped the Python code into an installer document.

Download the Writer document to a trusted directory allowing execution of embedded macros and push the install button. See Tools --> Options --> Security, button [Macro Security]. There you choose the highest security level and add one or more directories where you store trusted documents like this one. This must not be your default directory for downloads because this directory gets anything downloaded from the internet. You should explicitly move your trusted documents into trusted directories.

[Villeroy]

The time-stamp feature is typical for a database such as counter.odb
Databases of all kinds and derived record sets integrate perfectly in Calc, even better than values directly stored in the spreadsheet.

Testing the counter.odb with a spreadsheet:
Open the downloaded document.
Open the form therein.
Enter a few integer numbers (other values will be rejected).
There is no need to store anything. Databases write records to disk.
Select "Tables" in the main database window.
Open "Stamps" and see the time stamps and auto-IDs (to be used in further, interrelated tables).

Call menu:Tools>Options...OOo Base>Databases and register the database document.
Open a spreadsheet.
Call the beamer [key F4]
Browse the database>Tables>Stamps
Drag Stamps from the left pane into the spreadsheet.
menu:Data>Define...
Select "Import1"
and check options
[X] Insert/delete cells
[X] Keep formatting

Enter some formula in D2 (directly beneath the first "Value"), say =D2*2 and copy down the formula.
Apply any number formats you like to see in the import range. All values are guaranteed to be integers and date-times without gaps.

Edit some values using input form, the table or the beamer (invalid values and duplicate IDs will be rejected, the time stamps can be overwritten).
Spreadsheet-menu:Data>Refresh will refresh the import range.

Add some new integers into field "Value" using input form, the table or the beamer.
Spreadsheet-menu:Data>Refresh will refresh and resize the import range and the adjacent formula range as well.

Delete some records.
Spreadsheet-menu:Data>Refresh will refresh and resize the import range and the adjacent formula range as well.

Auto-resizing lists, including chart ranges and adjacent formulas, together with enforced consistancy, the ability to get arbitrary filtered and recombined data sets in arbitrary order of rows and columns, the fact that all data are stored in one place but usable in all Writer and Calc documents, including data pilots in Calc, without a single line of macro-code. All this can't be done using spreadsheets as the only tool.

[Villeroy]

Starting with the Calc-problem and a simplistic macro-solution, turning the view to a completely different approach (separation of storage and calculation model), now for one of the most frustrating shortcomings in OOo Base. The table designer in Base can not add time stamps as default values even though this is supported by the underlying HSQLDB.
Design your table without the stamp.
Call db-menu:Tools>SQL... and run

Code: Select all

ALTER TABLE "TableX" ADD COLUMN "Stamp" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL BEFORE "X";

For an already existing field that is:

Code: Select all

ALTER TABLE "TableX" ALTER COLUMN "Stamp" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL;

I used the double-quoted object names as follows:
"TableX" is the table in question.
"Stamp" the name of the stamp field to be added.
"X" is the name of another field. Leave out the clause BEFORE "X" in order to append the column.
NOT NULL makes the field mandatory so you can not delete an existing stamp (it can be edited though).
Use NOT NULL if and only if the table has no data yet, otherwise the insertion of this column will fail.
Using a form bound to a table with a time stamp, such as the above linked counter.odb, requires that you unset the following option:
db-menu:Edit>Database>AdvancedSettings... "Form input checks for required fields". Otherwise the form will reject to store the record with the missing date-value. With this option unset, the underlying HSQLDB takes care of the integrity, adding the field's default value.

Currently, I don't see any option to implement time stamps that update with every change of the record. Within Base this requires a simple macro to be installed. Any macro solution applies to input forms only without affecting the underlying database. You could edit a record in the table without triggering any macro.

The stand-alone version of HSQLDB supports triggers which can do all kinds of actions for update, delete and insert events. In the current Base implementation it is impossible to define triggers for the embedded HSQLDB.

But all this is still better than using spreadsheets as storage. Once you've got everything set up and linked, the combination of Base and Calc can solve many of the most frequent list keeping problems in Calc without further intervention.