Convert epoch time to human readable in LibreOffice Calc
I have a epoch time in cell H2 which has a value of 1517335200000. I am trying to convert it to a human readable format which should return 30/01/2018 6:00:00 PM in GMT.
I have tried to convert it with the formula H2/86400+25569 which I got from the OpenOffice forum. The formula returns the value 17587319. When I change the number format in LibreOffice Calc to Date, it returns the value of 06/09/-15484. That's not the value I want. So, how can I get the value in dd/mm/yyyy hh:mm:ss format form?
timestamps libreoffice
edited Feb 2 '18 at 5:38
JCDeen
403138
asked Feb 2 '18 at 5:21
Fazle RabbiFazle Rabbi
4717
add a comment |
I have a epoch time in cell H2 which has a value of 1517335200000. I am trying to convert it to a human readable format which should return 30/01/2018 6:00:00 PM in GMT.
I have tried to convert it with the formula H2/86400+25569 which I got from the OpenOffice forum. The formula returns the value 17587319. When I change the number format in LibreOffice Calc to Date, it returns the value of 06/09/-15484. That's not the value I want. So, how can I get the value in dd/mm/yyyy hh:mm:ss format form?
timestamps libreoffice
edited Feb 2 '18 at 5:38
JCDeen
403138
asked Feb 2 '18 at 5:21
Fazle RabbiFazle Rabbi
4717
add a comment |
I have a epoch time in cell H2 which has a value of 1517335200000. I am trying to convert it to a human readable format which should return 30/01/2018 6:00:00 PM in GMT.
I have tried to convert it with the formula H2/86400+25569 which I got from the OpenOffice forum. The formula returns the value 17587319. When I change the number format in LibreOffice Calc to Date, it returns the value of 06/09/-15484. That's not the value I want. So, how can I get the value in dd/mm/yyyy hh:mm:ss format form?
timestamps libreoffice
edited Feb 2 '18 at 5:38
JCDeen
403138
asked Feb 2 '18 at 5:21
Fazle RabbiFazle Rabbi
4717
I have a epoch time in cell H2 which has a value of 1517335200000. I am trying to convert it to a human readable format which should return 30/01/2018 6:00:00 PM in GMT.
I have tried to convert it with the formula H2/86400+25569 which I got from the OpenOffice forum. The formula returns the value 17587319. When I change the number format in LibreOffice Calc to Date, it returns the value of 06/09/-15484. That's not the value I want. So, how can I get the value in dd/mm/yyyy hh:mm:ss format form?
timestamps libreoffice
timestamps libreoffice
edited Feb 2 '18 at 5:38
JCDeen
403138
asked Feb 2 '18 at 5:21
Fazle RabbiFazle Rabbi
4717
edited Feb 2 '18 at 5:38
JCDeen
403138
asked Feb 2 '18 at 5:21
Fazle RabbiFazle Rabbi
4717
edited Feb 2 '18 at 5:38
JCDeen
403138
edited Feb 2 '18 at 5:38
JCDeen
403138
edited Feb 2 '18 at 5:38
JCDeen
403138
403138
asked Feb 2 '18 at 5:21
Fazle RabbiFazle Rabbi
4717
asked Feb 2 '18 at 5:21
Fazle RabbiFazle Rabbi
4717
asked Feb 2 '18 at 5:21
Fazle RabbiFazle Rabbi
4717
4717
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
If H2 contains the number to transform (1517335200000).
Make
H3contain the formula:
= H2/1000/(60*60*24) + 25569
Which will return the number 43130.75.
Change format of cell
H3to date. Either:
- Press Shift - Ctrl - 3
- Select Format --> Number Format --> Date
- Select Format --> Cells (a window opens) --> Numbers - Date - Format
- Press Shift - Ctrl - 3
Change format of the
H3cell to the required date format:
- Select Format --> Cells (a panel opens) --> Numbers - Date - Format (select one)
- Select Format --> Cells (a panel opens) --> Numbers - Date - Format (select one)
Expand width of cell if not wide enough to show the desired format (hint: three # appear).
Why:
Epoch time is in seconds since 1/1/1970.
Calc internal time is in days since 12/30/1899.
So, to get a correct result in H3:
Get the correct number (last formula):
H3 = H2/(60*60*24) + ( Difference to 1/1/1970 since 12/30/1899 in days )
H3 = H2/86400 + ( DATE (1970,1,1) - DATE(1899,12,30) )
H3 = H2/86400 + 25569
But the epoch value you are giving is too big, it is three zeros bigger than it should. Should be 1517335200 instead of 1517335200000. It seems to be giben in milliseconds. So, divide by 1000. With that change, the formula gives:
H3 = H2/1000/86400+25569 = 43130.75
Change the format of
H3to date and time (Format --> Cells --> Numbers --> Date --> Date and time) and you will see:
01/30/2018 18:00:00
in H3.
answered Feb 2 '18 at 5:58
IsaacIsaac
12k11852
1
I believe the the 3 zeros at the end are microsecond.
– Fazle Rabbi
Feb 2 '18 at 6:46
@FazleRabbi Then it is not an epoch time. Epoch time is in seconds. If you are sure that microseconds will be included in all the values you will use make the divisor86400*1000and the rest works the same.
– Isaac
Feb 2 '18 at 6:53
any way to add the value of12/30/1899without putting it on a cell? that will help me to make a one line formula so I may use it just by changing the value of H[0-9]. I got this far=(H2/(86400*1000))+25569
– Fazle Rabbi
Feb 2 '18 at 7:01
Format H3 as a date and time, that's the minimum needed. @FazleRabbi
– Isaac
Feb 2 '18 at 7:02
Damn this is awkward. Is there no simple way to assign a (Perl/Awk/Clojure/whatever) function to a cell? The Spreadsheet: A Bad Idea Implemented Badly since VisiCalc.
– David Tonhofer
2 days ago
|
show 2 more comments
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "106"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2funix.stackexchange.com%2fquestions%2f421354%2fconvert-epoch-time-to-human-readable-in-libreoffice-calc%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
If H2 contains the number to transform (1517335200000).
Make
H3contain the formula:
= H2/1000/(60*60*24) + 25569
Which will return the number 43130.75.
Change format of cell
H3to date. Either:
- Press Shift - Ctrl - 3
- Select Format --> Number Format --> Date
- Select Format --> Cells (a window opens) --> Numbers - Date - Format
- Press Shift - Ctrl - 3
Change format of the
H3cell to the required date format:
- Select Format --> Cells (a panel opens) --> Numbers - Date - Format (select one)
- Select Format --> Cells (a panel opens) --> Numbers - Date - Format (select one)
Expand width of cell if not wide enough to show the desired format (hint: three # appear).
Why:
Epoch time is in seconds since 1/1/1970.
Calc internal time is in days since 12/30/1899.
So, to get a correct result in H3:
Get the correct number (last formula):
H3 = H2/(60*60*24) + ( Difference to 1/1/1970 since 12/30/1899 in days )
H3 = H2/86400 + ( DATE (1970,1,1) - DATE(1899,12,30) )
H3 = H2/86400 + 25569
But the epoch value you are giving is too big, it is three zeros bigger than it should. Should be 1517335200 instead of 1517335200000. It seems to be giben in milliseconds. So, divide by 1000. With that change, the formula gives:
H3 = H2/1000/86400+25569 = 43130.75
Change the format of
H3to date and time (Format --> Cells --> Numbers --> Date --> Date and time) and you will see:
01/30/2018 18:00:00
in H3.
answered Feb 2 '18 at 5:58
IsaacIsaac
12k11852
1
I believe the the 3 zeros at the end are microsecond.
– Fazle Rabbi
Feb 2 '18 at 6:46
@FazleRabbi Then it is not an epoch time. Epoch time is in seconds. If you are sure that microseconds will be included in all the values you will use make the divisor86400*1000and the rest works the same.
– Isaac
Feb 2 '18 at 6:53
any way to add the value of12/30/1899without putting it on a cell? that will help me to make a one line formula so I may use it just by changing the value of H[0-9]. I got this far=(H2/(86400*1000))+25569
– Fazle Rabbi
Feb 2 '18 at 7:01
Format H3 as a date and time, that's the minimum needed. @FazleRabbi
– Isaac
Feb 2 '18 at 7:02
Damn this is awkward. Is there no simple way to assign a (Perl/Awk/Clojure/whatever) function to a cell? The Spreadsheet: A Bad Idea Implemented Badly since VisiCalc.
– David Tonhofer
2 days ago
|
show 2 more comments
If H2 contains the number to transform (1517335200000).
Make
H3contain the formula:
= H2/1000/(60*60*24) + 25569
Which will return the number 43130.75.
Change format of cell
H3to date. Either:
- Press Shift - Ctrl - 3
- Select Format --> Number Format --> Date
- Select Format --> Cells (a window opens) --> Numbers - Date - Format
- Press Shift - Ctrl - 3
Change format of the
H3cell to the required date format:
- Select Format --> Cells (a panel opens) --> Numbers - Date - Format (select one)
- Select Format --> Cells (a panel opens) --> Numbers - Date - Format (select one)
Expand width of cell if not wide enough to show the desired format (hint: three # appear).
Why:
Epoch time is in seconds since 1/1/1970.
Calc internal time is in days since 12/30/1899.
So, to get a correct result in H3:
Get the correct number (last formula):
H3 = H2/(60*60*24) + ( Difference to 1/1/1970 since 12/30/1899 in days )
H3 = H2/86400 + ( DATE (1970,1,1) - DATE(1899,12,30) )
H3 = H2/86400 + 25569
But the epoch value you are giving is too big, it is three zeros bigger than it should. Should be 1517335200 instead of 1517335200000. It seems to be giben in milliseconds. So, divide by 1000. With that change, the formula gives:
H3 = H2/1000/86400+25569 = 43130.75
Change the format of
H3to date and time (Format --> Cells --> Numbers --> Date --> Date and time) and you will see:
01/30/2018 18:00:00
in H3.
answered Feb 2 '18 at 5:58
IsaacIsaac
12k11852
1
I believe the the 3 zeros at the end are microsecond.
– Fazle Rabbi
Feb 2 '18 at 6:46
@FazleRabbi Then it is not an epoch time. Epoch time is in seconds. If you are sure that microseconds will be included in all the values you will use make the divisor86400*1000and the rest works the same.
– Isaac
Feb 2 '18 at 6:53
any way to add the value of12/30/1899without putting it on a cell? that will help me to make a one line formula so I may use it just by changing the value of H[0-9]. I got this far=(H2/(86400*1000))+25569
– Fazle Rabbi
Feb 2 '18 at 7:01
Format H3 as a date and time, that's the minimum needed. @FazleRabbi
– Isaac
Feb 2 '18 at 7:02
Damn this is awkward. Is there no simple way to assign a (Perl/Awk/Clojure/whatever) function to a cell? The Spreadsheet: A Bad Idea Implemented Badly since VisiCalc.
– David Tonhofer
2 days ago
|
show 2 more comments
If H2 contains the number to transform (1517335200000).
Make
H3contain the formula:
= H2/1000/(60*60*24) + 25569
Which will return the number 43130.75.
Change format of cell
H3to date. Either:
- Press Shift - Ctrl - 3
- Select Format --> Number Format --> Date
- Select Format --> Cells (a window opens) --> Numbers - Date - Format
- Press Shift - Ctrl - 3
Change format of the
H3cell to the required date format:
- Select Format --> Cells (a panel opens) --> Numbers - Date - Format (select one)
- Select Format --> Cells (a panel opens) --> Numbers - Date - Format (select one)
Expand width of cell if not wide enough to show the desired format (hint: three # appear).
Why:
Epoch time is in seconds since 1/1/1970.
Calc internal time is in days since 12/30/1899.
So, to get a correct result in H3:
Get the correct number (last formula):
H3 = H2/(60*60*24) + ( Difference to 1/1/1970 since 12/30/1899 in days )
H3 = H2/86400 + ( DATE (1970,1,1) - DATE(1899,12,30) )
H3 = H2/86400 + 25569
But the epoch value you are giving is too big, it is three zeros bigger than it should. Should be 1517335200 instead of 1517335200000. It seems to be giben in milliseconds. So, divide by 1000. With that change, the formula gives:
H3 = H2/1000/86400+25569 = 43130.75
Change the format of
H3to date and time (Format --> Cells --> Numbers --> Date --> Date and time) and you will see:
01/30/2018 18:00:00
in H3.
answered Feb 2 '18 at 5:58
IsaacIsaac
12k11852
If H2 contains the number to transform (1517335200000).
Make
H3contain the formula:
= H2/1000/(60*60*24) + 25569
Which will return the number 43130.75.
Change format of cell
H3to date. Either:
- Press Shift - Ctrl - 3
- Select Format --> Number Format --> Date
- Select Format --> Cells (a window opens) --> Numbers - Date - Format
- Press Shift - Ctrl - 3
Change format of the
H3cell to the required date format:
- Select Format --> Cells (a panel opens) --> Numbers - Date - Format (select one)
- Select Format --> Cells (a panel opens) --> Numbers - Date - Format (select one)
Expand width of cell if not wide enough to show the desired format (hint: three # appear).
Why:
Epoch time is in seconds since 1/1/1970.
Calc internal time is in days since 12/30/1899.
So, to get a correct result in H3:
Get the correct number (last formula):
H3 = H2/(60*60*24) + ( Difference to 1/1/1970 since 12/30/1899 in days )
H3 = H2/86400 + ( DATE (1970,1,1) - DATE(1899,12,30) )
H3 = H2/86400 + 25569
But the epoch value you are giving is too big, it is three zeros bigger than it should. Should be 1517335200 instead of 1517335200000. It seems to be giben in milliseconds. So, divide by 1000. With that change, the formula gives:
H3 = H2/1000/86400+25569 = 43130.75
Change the format of
H3to date and time (Format --> Cells --> Numbers --> Date --> Date and time) and you will see:
01/30/2018 18:00:00
in H3.
answered Feb 2 '18 at 5:58
IsaacIsaac
12k11852
edited 1 min ago
answered Feb 2 '18 at 5:58
IsaacIsaac
12k11852
answered Feb 2 '18 at 5:58
IsaacIsaac
12k11852
answered Feb 2 '18 at 5:58
IsaacIsaac
12k11852
12k11852
1
I believe the the 3 zeros at the end are microsecond.
– Fazle Rabbi
Feb 2 '18 at 6:46
@FazleRabbi Then it is not an epoch time. Epoch time is in seconds. If you are sure that microseconds will be included in all the values you will use make the divisor86400*1000and the rest works the same.
– Isaac
Feb 2 '18 at 6:53
any way to add the value of12/30/1899without putting it on a cell? that will help me to make a one line formula so I may use it just by changing the value of H[0-9]. I got this far=(H2/(86400*1000))+25569
– Fazle Rabbi
Feb 2 '18 at 7:01
Format H3 as a date and time, that's the minimum needed. @FazleRabbi
– Isaac
Feb 2 '18 at 7:02
Damn this is awkward. Is there no simple way to assign a (Perl/Awk/Clojure/whatever) function to a cell? The Spreadsheet: A Bad Idea Implemented Badly since VisiCalc.
– David Tonhofer
2 days ago
|
show 2 more comments
1
I believe the the 3 zeros at the end are microsecond.
– Fazle Rabbi
Feb 2 '18 at 6:46
@FazleRabbi Then it is not an epoch time. Epoch time is in seconds. If you are sure that microseconds will be included in all the values you will use make the divisor86400*1000and the rest works the same.
– Isaac
Feb 2 '18 at 6:53
any way to add the value of12/30/1899without putting it on a cell? that will help me to make a one line formula so I may use it just by changing the value of H[0-9]. I got this far=(H2/(86400*1000))+25569
– Fazle Rabbi
Feb 2 '18 at 7:01
Format H3 as a date and time, that's the minimum needed. @FazleRabbi
– Isaac
Feb 2 '18 at 7:02
Damn this is awkward. Is there no simple way to assign a (Perl/Awk/Clojure/whatever) function to a cell? The Spreadsheet: A Bad Idea Implemented Badly since VisiCalc.
– David Tonhofer
2 days ago
1
1
I believe the the 3 zeros at the end are microsecond.
– Fazle Rabbi
Feb 2 '18 at 6:46
I believe the the 3 zeros at the end are microsecond.
– Fazle Rabbi
Feb 2 '18 at 6:46
@FazleRabbi Then it is not an epoch time. Epoch time is in seconds. If you are sure that microseconds will be included in all the values you will use make the divisor
86400*1000 and the rest works the same.– Isaac
Feb 2 '18 at 6:53
@FazleRabbi Then it is not an epoch time. Epoch time is in seconds. If you are sure that microseconds will be included in all the values you will use make the divisor
86400*1000 and the rest works the same.– Isaac
Feb 2 '18 at 6:53
any way to add the value of
12/30/1899 without putting it on a cell? that will help me to make a one line formula so I may use it just by changing the value of H[0-9]. I got this far =(H2/(86400*1000))+25569– Fazle Rabbi
Feb 2 '18 at 7:01
any way to add the value of
12/30/1899 without putting it on a cell? that will help me to make a one line formula so I may use it just by changing the value of H[0-9]. I got this far =(H2/(86400*1000))+25569– Fazle Rabbi
Feb 2 '18 at 7:01
Format H3 as a date and time, that's the minimum needed. @FazleRabbi
– Isaac
Feb 2 '18 at 7:02
Format H3 as a date and time, that's the minimum needed. @FazleRabbi
– Isaac
Feb 2 '18 at 7:02
Damn this is awkward. Is there no simple way to assign a (Perl/Awk/Clojure/whatever) function to a cell? The Spreadsheet: A Bad Idea Implemented Badly since VisiCalc.
– David Tonhofer
2 days ago
Damn this is awkward. Is there no simple way to assign a (Perl/Awk/Clojure/whatever) function to a cell? The Spreadsheet: A Bad Idea Implemented Badly since VisiCalc.
– David Tonhofer
2 days ago
|
show 2 more comments
Thanks for contributing an answer to Unix & Linux Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2funix.stackexchange.com%2fquestions%2f421354%2fconvert-epoch-time-to-human-readable-in-libreoffice-calc%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
