Page 1 of 1

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

PostPosted: Wed Oct 02, 2019 1:29 am
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   Expand viewCollapse view
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.

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

PostPosted: Wed Oct 02, 2019 2:12 am
by JeJe
Have you tried cell*TIMEVALUE("0:00.001")+DATEVALUE("1970-01-01") ?

viewtopic.php?f=13&t=606

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

PostPosted: Wed Oct 02, 2019 2:35 am
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?"

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

PostPosted: Wed Oct 02, 2019 2:44 am
by JeJe
This works for me,

Code: Select all   Expand viewCollapse view
'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   


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

PostPosted: Wed Oct 02, 2019 2:53 am
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

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

PostPosted: Wed Oct 02, 2019 4:01 am
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

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

PostPosted: Wed Oct 02, 2019 6:00 am
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.)

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

PostPosted: Wed Oct 02, 2019 8:42 am
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   Expand viewCollapse view
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 ;)

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

PostPosted: Wed Oct 02, 2019 8:53 am
by Zizi64
example.ods


Yes, all of values are text from the row 5. You must convert them to numeric value first.

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

PostPosted: Wed Oct 02, 2019 9:16 am
by Zizi64
A macro cell function written in LibreOffice 6.1.6:

example_Zizi64.ods
(19.63 KiB) Downloaded 16 times

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

PostPosted: Wed Oct 02, 2019 1:48 pm
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.

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

PostPosted: Wed Oct 02, 2019 8:46 pm
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.

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

PostPosted: Wed Oct 02, 2019 9:03 pm
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