[Solved] Macro to convert 13 digit UNIX time/date

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
User avatar
thatguytwo
Posts: 5
Joined: Wed Oct 02, 2019 1:10 am

[Solved] Macro to convert 13 digit UNIX time/date

Post by thatguytwo »

Im trying to get this macro to work. It is supposed to take a 13 digit 'UNIX' time stamp that exists in column A, and convert it to MM:DD:YYYY HH:MM:SS for all the rows in the current sheet.

I have already preformatted every cell in the column as :
Category: Date
Format: 12/31/1999 13:37:46

Ive tried just inputting the simple =cells value/86400000+25569 in A1, and dragging the little square on the selection box down the whole column, but that doesnt work.

Heres the macro im trying to get to work:

Code: Select all

Sub Main
Dim oCell
with ThisComponent.CurrentController.ActiveSheet
for lRow = 0 to 400
 oCell = .getCellByPosition(0, lRow)
 oCell.setValue(oCell.getValue()/86400000+25569)
 next
end with
end Sub
but it just sets every cells value (in that column) to " 01/01/1970 00:00:00 "


Can someone assist me with this? Especially having it run through each row with a single macro run.
Last edited by thatguytwo on Wed Oct 02, 2019 8:47 pm, edited 1 time in total.
OpenOffice 3.1 on Windows 10
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Macro to convert 13 digit UNIX time/date to other format

Post by JeJe »

Have you tried cell*TIMEVALUE("0:00.001")+DATEVALUE("1970-01-01") ?

viewtopic.php?f=13&t=606
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
User avatar
thatguytwo
Posts: 5
Joined: Wed Oct 02, 2019 1:10 am

Re: Macro to convert 13 digit UNIX time/date to other format

Post by thatguytwo »

I did try having the cell highlighted, then clicking the function button, then copy pasting both

cell*TIMEVALUE("0:00.001")+DATEVALUE("1970-01-01")

then trying this instead..

cell/86400000+25569


and with both I get the error " #NAME?"
OpenOffice 3.1 on Windows 10
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Macro to convert 13 digit UNIX time/date to other format

Post by JeJe »

This works for me,

Code: Select all

'1443852054000 pasted into cell 0,0
v= ThisComponent.CurrentController.ActiveSheet.getCellByPosition(0, 0).value 
v = v*TIMEVALUE("0:00.001")+DATEVALUE("1970-01-01")
 ThisComponent.CurrentController.ActiveSheet.getCellByPosition(0, 0).string =v

' cell changed to 03/10/2015 06:00:54	

Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
User avatar
thatguytwo
Posts: 5
Joined: Wed Oct 02, 2019 1:10 am

Re: Macro to convert 13 digit UNIX time/date to other format

Post by thatguytwo »

i inserted the 3 lines of code between my for line and next replacing

getCellByPosition(0, 0)

with

getCellByPosition(0, lRow)

and it turns all cells in that column to 01/01/1970
OpenOffice 3.1 on Windows 10
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Macro to convert 13 digit UNIX time/date to other format

Post by MrProgrammer »

thatguytwo wrote:It is supposed to take a 13 digit 'UNIX' time stamp that exists in column A …
UNIX timestamps are never 13 digits. Any current timestamps are ten digits.
"Unix time passed 1000000000 seconds in 2001-09-09T01:46:40Z." UNIX time
thatguytwo wrote:Im trying to get this macro to work
Trying to do this with an evil macro will make the job ten times harder. Just use a formula.
thatguytwo wrote:I did try having the cell highlighted, then clicking the function button, then copy pasting both
cell*TIMEVALUE("0:00.001")+DATEVALUE("1970-01-01") [or] cell/86400000+25569 and with both I get the error " #NAME?"
You have used a name that Calc does not recognize.
• In your formula replace "cell" with the actual cell address, say A1, or DX65537.
• DATEVALUE and TIMEVALUE are the function names in English. If your OpenOffice uses a different language, you have to use those function names. For example DATEVAL and TEMPSVAL for French, FECHANÚMERO and HORANÚMERO for Spanish. Use F1 help to determine function names in the installed language.

Begin your formula with an equal sign "=".
JeJe wrote:Have you tried cell*TIMEVALUE("0:00.001")+DATEVALUE("1970-01-01") ?
That formula is only for the bogus time stamp from bns002.OOConfused in topic Convert UNIX timestamp. For a real UNIX timestamp you would use something like =A1*TIMEVALUE("0:00:01")+DATEVALUE("1970-01-01") or =E314/86400+25569.
thatguytwo wrote:Ive tried just inputting the simple =cells value/86400000+25569 in A1, and dragging the little square on the selection box down the whole column, but that doesnt work.
"It didn't work" isn't helpful in the forum because it tells us what did not happen. Please never use that phrase in a post. What did happen?
• Error message? Give us the exact message.
• Your computer powered itself off? Probably not an OpenOffice problem.
• Strange results? Read the paragraph above about the bogus timestamp.

Attaching a document demonstrating the problem is almost always helpful for us and will get your problem solved more quickly. Often "it didn't work" means "it didn't do what I expected" and the real problem is that the program is behaving correctly but your expectations are incorrect. To help you solve the problem we need to know your expectations and your ultimate goal.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Macro to convert 13 digit UNIX time/date to other format

Post by Zizi64 »


Re: Macro to convert 13 digit UNIX time/date to other format

Unread postby thatguytwo » 2019 Oct 02, 2:53 am
i inserted the 3 lines of code between my for line and next replacing

getCellByPosition(0, 0)

with

getCellByPosition(0, lRow)

and it turns all cells in that column to 01/01/1970
Please upload your sample file here together the embedded macro.

(Maybe your Unix Date values are strings - with zero value - but not numeric values.)
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
thatguytwo
Posts: 5
Joined: Wed Oct 02, 2019 1:10 am

Re: Macro to convert 13 digit UNIX time/date to other format

Post by thatguytwo »

MrProgrammer wrote:
thatguytwo wrote:Im trying to get this macro to work
Trying to do this with an evil macro will make the job ten times harder. Just use a formula.
I was unsure which would be the best, i didnt understand the differentiation between the two terms. I thought a macro was an automated formula(s).
MrProgrammer wrote:
thatguytwo wrote:I did try having the cell highlighted, then clicking the function button, then copy pasting both
cell*TIMEVALUE("0:00.001")+DATEVALUE("1970-01-01") [or] cell/86400000+25569 and with both I get the error " #NAME?"
You have used a name that Calc does not recognize.
• In your formula replace "cell" with the actual cell address, say A1, or DX65537.
• DATEVALUE and TIMEVALUE are the function names in English. If your OpenOffice uses a different language, you have to use those function names. For example DATEVAL and TEMPSVAL for French, FECHANÚMERO and HORANÚMERO for Spanish. Use F1 help to determine function names in the installed language.
No, i did not replace "cell" with the cell address. =A1/86400000+25569 seemed pointless because I am trying to automate it over hundreds of rows. So using exact addresses...well i dont understand why I would do that if im trying to affect multiple rows.
MrProgrammer wrote: Begin your formula with an equal sign "=".
I thought it was assumed I would do that.

MrProgrammer wrote:
thatguytwo wrote:Ive tried just inputting the simple =cells value/86400000+25569 in A1, and dragging the little square on the selection box down the whole column, but that doesnt work.
"It didn't work" isn't helpful in the forum because it tells us what did not happen. Please never use that phrase in a post. What did happen?
• Error message? Give us the exact message.
• Your computer powered itself off? Probably not an OpenOffice problem.
• Strange results? Read the paragraph above about the bogus timestamp.

Attaching a document demonstrating the problem is almost always helpful for us and will get your problem solved more quickly. Often "it didn't work" means "it didn't do what I expected" and the real problem is that the program is behaving correctly but your expectations are incorrect. To help you solve the problem we need to know your expectations and your ultimate goal.
Stating such a nebulous term "it didnt work" is a faux pas, but I considered the output it gave me was incorrect, so to state that result when that tactic was to me not getting the expected result didnt seem important

To be more exacting, when i do drag the square down the column, it will just copy the value from the initial selected row into the rows 'dragged' to.

Code: Select all

a1 08/28/2019 16:54:53 (start drag here)

a2 08/28/2019 16:54:53
a3 08/28/2019 16:54:53
a4 08/28/2019 16:54:53


I have uploaded a small piece of my document. As you can see the first rows of the target column (A) have been changed to the correct output during various testing stages. Im leaving them like that to give an idea to how the rest of the rows should look upon correct output.
Zizi64 wrote: Please upload your sample file here together the embedded macro.

(Maybe your Unix Date values are strings - with zero value - but not numeric values.)
If the macro was not embedded in my upload, the exact text in the macro is in my first post, just include REM *****BASIC***** at the top ;)
Attachments
example.ods
(14.08 KiB) Downloaded 194 times
OpenOffice 3.1 on Windows 10
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Macro to convert 13 digit UNIX time/date to other format

Post by Zizi64 »

example.ods
Yes, all of values are text from the row 5. You must convert them to numeric value first.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Macro to convert 13 digit UNIX time/date to other format

Post by Zizi64 »

A macro cell function written in LibreOffice 6.1.6:
example_Zizi64.ods
(19.63 KiB) Downloaded 218 times
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro to convert 13 digit UNIX time/date to other format

Post by Villeroy »

Dates in spreadsheets are day numbers.
=A1/86400000+"1970-01-01" converts the milliseconds of cell A1 into days and adds the day number of 1970-01-01. Format the result any way you want.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
thatguytwo
Posts: 5
Joined: Wed Oct 02, 2019 1:10 am

Re: Macro to convert 13 digit UNIX time/date to other format

Post by thatguytwo »

Zizi64 wrote:A macro cell function written in LibreOffice 6.1.6:
example_Zizi64.ods
Thank you, it took me a while to get it working, because I didnt understand exactly how it functioned. but you simply =CONVERTFROMUNIX() in another cell, and () the first target cell, then drag the square over the rows you want. Originally before coming here I had poured over documentation for 4ish hours and tried many things, so im glad you provided a straight answer. Ooo syntax, functions and even VB isnt my thing; so im glad I didnt need to take on a new skill for this one simple thing. My head is too full already.
OpenOffice 3.1 on Windows 10
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: [SOLVED] Macro to convert 13 digit UNIX time/date

Post by Zizi64 »

Ooo syntax, functions and even VB isnt my thing;
It is not VB.
It is the AOO/LO API and the StarBasic language and IDE
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Post Reply