[Solved] Timestamp

[NewToOpenOffice]

So I'm trying to make a log and I can't seem to figure out how to make an automatically generated timestamp each time I make an entry. Please help?

[RoryOF]

Are you using Writer or Calc?

[NewToOpenOffice]

The um... the one that just says Open Office? It's like a blue circle with white... birds?

[Zizi64]

Try the LibreOffice, and use the existing "Insert - Time" and "Insert - Date" function of the LO Calc.

Or you can write your own macro for this task in Apache OpenOffice.

[NewToOpenOffice]

LibreOffice?

[Villeroy]

yes, https://libreoffice.org

If you are working with a database in either LibreOffice or OpenOffice, you can call Tools>SQL... and issue the following command:
{code]ALTER TABLE "Table Name" ALTER COLUMN "Column Name" SET DEFAULT CURRENT_TIMESTAMP[/code]
replace "Table Name" and "Column Name" with the names of your actual table and column.

[NewToOpenOffice]

Oh, I have to make a table? At work, we use excel and when we scan bar codes, it shows up on the spreadsheet and automatically records the time for each scan.

[RoryOF]

The um... the one that just says Open Office? It's like a blue circle with white... birds?

OpenOffice is a grouping of several applications, including Writer (for text documents), Calc (a spreadsheet, for numeric calculations), Impress (to make Presentations) and others.

To assist you, we need to know which of these you are using; on the title bar at the top of the Window in use it should show to the right OpenOffice XXXX, where XXXX is the name of the application.

[NewToOpenOffice]

Well, I'm trying to use OpenOffice's alternative to Excel, so Calc?

[NewToOpenOffice]

So... timestamps are impossible?

[Villeroy]

So I'm trying to make a log and I can't seem to figure out how to make an automatically generated timestamp each time I make an entry. Please help?

This sounds very much like a database feature, therefore I assumed that you were using Base, the database component. IMHO, Excel can't do that without macro code. As far as I know, Excel can insert a timestamp into the current cell when you hit a certain keyboard shortcut. Is this what you want?

[NewToOpenOffice]

Basically, what I want is to be able to make a timestamp automatically generate everytime I make a new entry. For example, something like...
5:01 PM / Started to wash dishes
5:15 PM / Finished washing dishes

Something like that.

[Villeroy]

This is not an Excel feature. It is a typical database feature.
It is possible to write a macro program just like the macro program you might have used in MS Excel.
If you write something in some cell, you need to specify the column where the time stamp should appear within the same row.

I almost forgot one trick that works in Excel and Calc without macro programming:
menu:Tools>Options...OOoCalc>Calculate: Enable iteration with 1 step
Cell A1: =IF(B1="";"";IF(A1="";NOW();A1))
Now you get a timestamp in A1 when you enter something in B1.
You get a new timestamp when you clear B1 (backspace key) and then enter something new.

[NewToOpenOffice]

Oh wow! Thanks for your help! I'll try it!

[NewToOpenOffice]

YAY! You solved it! This is exactly what I wanted! Thank you so much!

[Zizi64]

YAY! You solved it! This is exactly what I wanted! Thank you so much!

Note: if you modify tha iteration settings (even accidently) , all of the appeared dates will be modified - when you use that tricky method.

(You can insert pure date values - instead of the formulas - by a macro.)

[Lupp]

+1 @Zizi64

Over the years there were dicussions now and then about timestamping by workarounds based on"conditionally suspended circular references" as I prefer to call them. Years ago I also suggested some extended variants of it - even including complete histories concerning the values entered into a specific cell. Though and because I put some time in studying the topic I decided to never again suggest such a solution. It never can be reliable for more than one reason. Zizi64 pointed out an important one.

If you ("NewToOpenOffice") insist on deciding on your own, and come to the conclusion to use the mentioned workaround, don't apply it in cases where high reliability is required. And: Related issues surely are not "beginners" matter.

Best you abandon the idea of timestamps concerning the editing of cells in spreadsheets.
Based on a couple of Sub I have at hand anyway, I give an example for a solution by "macros" (as Zizi64 also hinted). It would still need a lot of enhancement to get actually usable - and would never match the reliability you get for free when using a database.

(I am also interested in refutations of the solution contained in the attachment.)

[NewToOpenOffice]

Oh... well it's worked the way I've wanted it to so far

[Villeroy]

How did you do it with MS Excel?

[BarcodeArt]

"high reliability is required"

I downloaded Lupp's TimeStamping.ods document, and the macro works great, but I want to add milliseconds to the time stamp. I tried editing the cell format date, but I do not know what to put after the seconds YYYY-MM-DD HH:MM:SS.???

I hacked together a timing system using Google Sheets, with Script Editor code from the InternetGeeks, and it works fine, but I'd prefer to auto insert time stamp with milliseconds in OpenOffice Calc.

function onEdit(event)
{
var timezone = "GMT-5";
var timestamp_format = "HH:mm:ss:SSS"; // Timestamp Format.
var updateColName = "Sensor";
var timeStampColName = "Timestamp";
var sheet = event.source.getSheetByName('Sheet1'); //Name of the sheet where you want to run this script.


var actRng = event.source.getActiveRange();
var editColumn = actRng.getColumn();
var index = actRng.getRowIndex();
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();
var dateCol = headers[0].indexOf(timeStampColName);
var updateCol = headers[0].indexOf(updateColName); updateCol = updateCol+1;
if (dateCol > -1 && index > 1 && editColumn == updateCol) { // only timestamp if 'Last Updated' header exists, but not in the header row itself!
var cell = sheet.getRange(index, dateCol + 1);
var date = Utilities.formatDate(new Date(), timezone, timestamp_format);
cell.setValue(date);
}
}

[robleyd]

Help shows this for time formatting:

To display seconds as fractions, add the decimal delimiter to your number format code.
For example, enter hh:mm:ss.00 to display the time as "01:02:03.45".

[BarcodeArt]

For example, enter hh:mm:ss.00 to display the time as "01:02:03.45".

Thank you robleyd, but changing the format to hh:mm:ss.00 does not work on Lupp's TimeStamping.ods document. The fractions of seconds are all zero. I think the macro needs to be edited, but I cannot find where the timestamp code is.

[Villeroy]

enter =1/2 into some cell. What is the decimal delimiter?

[Zizi64]

changing the format to hh:mm:ss.00 does not work on Lupp's TimeStamping.ods document. The fractions of seconds are all zero. I think the macro needs to be edited, but I cannot find where the timestamp code is.

The NOW() function of the StarBasic rounds the DateTime value to seconds. Use the Calc Cell function "NOW()" instead.

Code: Select all

 Dim Dummy()
	Dim svc As Object
	svc = createUnoService("com.sun.star.sheet.FunctionAccess") 
	rightNeighbour.Value = svc.callFunction("NOW",Dummy())