Page 1 of 1

[Solved] Timestamp

Posted: Mon Sep 03, 2018 8:33 pm
by 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?

Re: Timestamp

Posted: Mon Sep 03, 2018 8:56 pm
by RoryOF
Are you using Writer or Calc?

Re: Timestamp

Posted: Mon Sep 03, 2018 8:58 pm
by NewToOpenOffice
The um... the one that just says Open Office? It's like a blue circle with white... birds?

Re: Timestamp

Posted: Mon Sep 03, 2018 9:01 pm
by 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.

Re: Timestamp

Posted: Mon Sep 03, 2018 9:03 pm
by NewToOpenOffice
LibreOffice?

Re: Timestamp

Posted: Mon Sep 03, 2018 9:10 pm
by 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.

Re: Timestamp

Posted: Mon Sep 03, 2018 9:12 pm
by 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.

Re: Timestamp

Posted: Mon Sep 03, 2018 9:34 pm
by RoryOF
NewToOpenOffice wrote: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.

Re: Timestamp

Posted: Mon Sep 03, 2018 9:41 pm
by NewToOpenOffice
Well, I'm trying to use OpenOffice's alternative to Excel, so Calc?

Re: Timestamp

Posted: Mon Sep 03, 2018 10:41 pm
by NewToOpenOffice
So... timestamps are impossible?

Re: Timestamp

Posted: Mon Sep 03, 2018 10:55 pm
by Villeroy
NewToOpenOffice wrote: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?

Re: Timestamp

Posted: Mon Sep 03, 2018 11:02 pm
by 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.

Re: Timestamp

Posted: Mon Sep 03, 2018 11:49 pm
by 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.

Re: Timestamp

Posted: Tue Sep 04, 2018 1:06 am
by NewToOpenOffice
Oh wow! Thanks for your help! I'll try it!

Re: Timestamp

Posted: Tue Sep 04, 2018 1:20 am
by NewToOpenOffice
YAY! You solved it! This is exactly what I wanted! Thank you so much!

Re: Timestamp

Posted: Tue Sep 04, 2018 7:12 am
by 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.)

Re: [Solved] Timestamp

Posted: Tue Sep 04, 2018 11:06 am
by 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.)

Re: [Solved] Timestamp

Posted: Tue Sep 04, 2018 4:12 pm
by NewToOpenOffice
Oh... well it's worked the way I've wanted it to so far

Re: [Solved] Timestamp

Posted: Tue Sep 04, 2018 5:21 pm
by Villeroy
How did you do it with MS Excel?

Re: [Solved] Timestamp

Posted: Mon Oct 15, 2018 7:59 pm
by BarcodeArt
Lupp wrote:"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.
InternetGeeks wrote: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);
}
}

Re: [Solved] Timestamp

Posted: Tue Oct 16, 2018 1:42 am
by 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".

Re: [Solved] Timestamp

Posted: Tue Oct 16, 2018 4:47 pm
by BarcodeArt
robleyd wrote: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.

Re: [Solved] Timestamp

Posted: Wed Oct 17, 2018 7:29 pm
by Villeroy
enter =1/2 into some cell. What is the decimal delimiter?

Re: [Solved] Timestamp

Posted: Wed Oct 17, 2018 8:05 pm
by 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())