Mixing
Converting time stamps in excel to dates
I have a very large excel spread sheet that has a column of time stamps. Does anyone know convert that over to a date? Is there a function I can use? I tried format cell date but that doesn't work. My file is 91,568 KB. If there is a simpler way to this that would be great. I'm open to ideas.
Thank you in advance :)
P.S.
I don't know any programming languages
excel-formula
asked Apr 17 '13 at 21:50
user10165user10165
4652610
add a comment |
I have a very large excel spread sheet that has a column of time stamps. Does anyone know convert that over to a date? Is there a function I can use? I tried format cell date but that doesn't work. My file is 91,568 KB. If there is a simpler way to this that would be great. I'm open to ideas.
Thank you in advance :)
P.S.
I don't know any programming languages
excel-formula
asked Apr 17 '13 at 21:50
user10165user10165
4652610
1
What kind of a timestamp is that? Milliseconds? Internet is full of answers. Did you try some of those? bajb.net/2010/05/excel-timestamp-to-date
– NeplatnyUdaj
Apr 17 '13 at 21:51
The time stamp looks like this 1234817823. When I use this website (onlineconversion.com/unix_time.htm) it says Mon, 16 Feb 2009 20:57:03 GMT. My problem is how to create a function that will apply to 1 million records.
– user10165
Apr 17 '13 at 22:02
1
Then have a look at the link I've posted. I don't have excel, but I've tried that in Libreoffice Calc and the output is: 02/16/09 08:57 PM
– NeplatnyUdaj
Apr 17 '13 at 22:05
Up it works thank you soo much!!! :)
– user10165
Apr 17 '13 at 22:09
add a comment |
I have a very large excel spread sheet that has a column of time stamps. Does anyone know convert that over to a date? Is there a function I can use? I tried format cell date but that doesn't work. My file is 91,568 KB. If there is a simpler way to this that would be great. I'm open to ideas.
Thank you in advance :)
P.S.
I don't know any programming languages
excel-formula
asked Apr 17 '13 at 21:50
user10165user10165
4652610
I have a very large excel spread sheet that has a column of time stamps. Does anyone know convert that over to a date? Is there a function I can use? I tried format cell date but that doesn't work. My file is 91,568 KB. If there is a simpler way to this that would be great. I'm open to ideas.
Thank you in advance :)
P.S.
I don't know any programming languages
excel-formula
excel-formula
asked Apr 17 '13 at 21:50
user10165user10165
4652610
asked Apr 17 '13 at 21:50
user10165user10165
4652610
asked Apr 17 '13 at 21:50
user10165user10165
4652610
asked Apr 17 '13 at 21:50
user10165user10165
4652610
asked Apr 17 '13 at 21:50
user10165user10165
4652610
4652610
1
What kind of a timestamp is that? Milliseconds? Internet is full of answers. Did you try some of those? bajb.net/2010/05/excel-timestamp-to-date
– NeplatnyUdaj
Apr 17 '13 at 21:51
The time stamp looks like this 1234817823. When I use this website (onlineconversion.com/unix_time.htm) it says Mon, 16 Feb 2009 20:57:03 GMT. My problem is how to create a function that will apply to 1 million records.
– user10165
Apr 17 '13 at 22:02
1
Then have a look at the link I've posted. I don't have excel, but I've tried that in Libreoffice Calc and the output is: 02/16/09 08:57 PM
– NeplatnyUdaj
Apr 17 '13 at 22:05
Up it works thank you soo much!!! :)
– user10165
Apr 17 '13 at 22:09
add a comment |
1
What kind of a timestamp is that? Milliseconds? Internet is full of answers. Did you try some of those? bajb.net/2010/05/excel-timestamp-to-date
– NeplatnyUdaj
Apr 17 '13 at 21:51
The time stamp looks like this 1234817823. When I use this website (onlineconversion.com/unix_time.htm) it says Mon, 16 Feb 2009 20:57:03 GMT. My problem is how to create a function that will apply to 1 million records.
– user10165
Apr 17 '13 at 22:02
1
Then have a look at the link I've posted. I don't have excel, but I've tried that in Libreoffice Calc and the output is: 02/16/09 08:57 PM
– NeplatnyUdaj
Apr 17 '13 at 22:05
Up it works thank you soo much!!! :)
– user10165
Apr 17 '13 at 22:09
1
1
What kind of a timestamp is that? Milliseconds? Internet is full of answers. Did you try some of those? bajb.net/2010/05/excel-timestamp-to-date
– NeplatnyUdaj
Apr 17 '13 at 21:51
What kind of a timestamp is that? Milliseconds? Internet is full of answers. Did you try some of those? bajb.net/2010/05/excel-timestamp-to-date
– NeplatnyUdaj
Apr 17 '13 at 21:51
The time stamp looks like this 1234817823. When I use this website (onlineconversion.com/unix_time.htm) it says Mon, 16 Feb 2009 20:57:03 GMT. My problem is how to create a function that will apply to 1 million records.
– user10165
Apr 17 '13 at 22:02
The time stamp looks like this 1234817823. When I use this website (onlineconversion.com/unix_time.htm) it says Mon, 16 Feb 2009 20:57:03 GMT. My problem is how to create a function that will apply to 1 million records.
– user10165
Apr 17 '13 at 22:02
1
1
Then have a look at the link I've posted. I don't have excel, but I've tried that in Libreoffice Calc and the output is: 02/16/09 08:57 PM
– NeplatnyUdaj
Apr 17 '13 at 22:05
Then have a look at the link I've posted. I don't have excel, but I've tried that in Libreoffice Calc and the output is: 02/16/09 08:57 PM
– NeplatnyUdaj
Apr 17 '13 at 22:05
Up it works thank you soo much!!! :)
– user10165
Apr 17 '13 at 22:09
Up it works thank you soo much!!! :)
– user10165
Apr 17 '13 at 22:09
add a comment |
11 Answers
11
active
oldest
votes
Use this formula and set formatting to the desired time format:
=(((COLUMN_ID_HERE/60)/60)/24)+DATE(1970,1,1)
Source: http://www.bajb.net/2010/05/excel-timestamp-to-date/
Tested in libreoffice
answered Apr 17 '13 at 22:07
NeplatnyUdajNeplatnyUdaj
3,27832965
2
=INT(((COLUMN_ID_HERE/60)/60)/24)+DATE(1970,1,1) This works for me.. Also Note that the Timestamp should be in seconds
– nischayn22
Aug 10 '16 at 14:32
add a comment |
If you get a Error 509 in Libre office you may replace , by ; in the DATE() function
=(((COLUMN_ID_HERE/60)/60)/24)+DATE(1970;1;1)
answered Apr 24 '14 at 20:36
Karl AdlerKarl Adler
6,88934262
5
This may depend on the locales settings in your OS.
– lorenz
Oct 31 '14 at 10:21
add a comment |
If your file is really big try to use following formula:
=A1 / 86400 + 25569
A1 should be replaced to what your need.
Should work faster than =(((COLUMN_ID_HERE/60)/60)/24)+DATE(1970,1,1) cause of less number of calculations needed.
answered Feb 22 '14 at 17:29
Alexey MatskoffAlexey Matskoff
35426
This is a more elegant solution and also keeps the 'time' part of the timestamp (not just the date)
– Patrick
May 28 '17 at 3:14
add a comment |
=(((A1/60)/60)/24)+DATE(1970,1,1)+(-5/24)
assuming A1 is the cell where your time stamp is located and dont forget to adjust to account for the time zone you are in (5 assuming you are on EST)
answered Apr 15 '14 at 19:51
LanaLana
7111
add a comment |
This DATE-thing won't work in all Excel-versions.
=CELL_ID/(60 * 60 * 24) + "1/1/1970"
is a save bet instead.
The quotes are necessary to prevent Excel from calculating the term.
answered Sep 19 '16 at 6:21
Stephan WeinholdStephan Weinhold
1,25911732
add a comment |
A timestamp is the elapsed time since Epoch time (01/01/1970), so basically we have to convert this time in days, and add the epoch time, to get a valid format for any Excel like spreadsheet software.
From a timestamp in milliseconds (ex: 1488380243994)
use this formula:
=A1/1000/86400+25569
with this formater:
yyyy-mm-dd hh:mm:ss.000
From a timestamp in seconds (ex: 1488380243)
use this formula:
=A1/86400+25569
with this formater:
yyyy-mm-dd hh:mm:ss
Where A1 is your column identifier.
Given custom formaters allow to not loose precision in displayed data, but you can of course use any other date/time one that corresponds to your needs.
answered Oct 18 '18 at 14:21
DonatelloDonatello
1,73621521
add a comment |
below formula worked form me in MS EXEL
=TEXT(CELL_VALUE/24/60/60/1000 + 25569,"YYYY-MM-DD HH:MM")
CELL_VALUE is timestamp in milliseconds
here is explanation for text function.
answered Nov 22 '18 at 10:31
ranjeetcaoranjeetcao
92621120
Could you add some explanation to the code?
– Federico Grandi
Nov 22 '18 at 18:11
add a comment |
AD ticks to datetime format: =A1/864000000000 - 109205
answered Dec 15 '15 at 13:20
Evgeniy NikulovEvgeniy Nikulov
9
add a comment |
i got result from this in LibreOffice Calc :
=DATE(1970,1,1)+Column_id_here/60/60/24
answered Jan 31 '16 at 7:30
Samir SayyadSamir Sayyad
112
add a comment |
This worked for me:
=(col_name]/60/60/24)+(col_name-1)
edited Mar 3 '17 at 10:45
deblocker
5,12721334
answered Mar 3 '17 at 8:28
Aparna KamathAparna Kamath
1
add a comment |
The answer of @NeplatnyUdaj is right but consider that Excel want the function name in the set language, in my case German. Then you need to use "DATUM" instead of "DATE":
=(((COLUMN_ID_HERE/60)/60)/24)+DATUM(1970,1,1)
answered Oct 18 '18 at 14:44
ownkingownking
1,40311826
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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%2fstackoverflow.com%2fquestions%2f16070868%2fconverting-time-stamps-in-excel-to-dates%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
11 Answers
11
active
oldest
votes
11 Answers
11
active
oldest
votes
active
oldest
votes
active
oldest
votes
Use this formula and set formatting to the desired time format:
=(((COLUMN_ID_HERE/60)/60)/24)+DATE(1970,1,1)
Source: http://www.bajb.net/2010/05/excel-timestamp-to-date/
Tested in libreoffice
answered Apr 17 '13 at 22:07
NeplatnyUdajNeplatnyUdaj
3,27832965
2
=INT(((COLUMN_ID_HERE/60)/60)/24)+DATE(1970,1,1) This works for me.. Also Note that the Timestamp should be in seconds
– nischayn22
Aug 10 '16 at 14:32
add a comment |
Use this formula and set formatting to the desired time format:
=(((COLUMN_ID_HERE/60)/60)/24)+DATE(1970,1,1)
Source: http://www.bajb.net/2010/05/excel-timestamp-to-date/
Tested in libreoffice
answered Apr 17 '13 at 22:07
NeplatnyUdajNeplatnyUdaj
3,27832965
2
=INT(((COLUMN_ID_HERE/60)/60)/24)+DATE(1970,1,1) This works for me.. Also Note that the Timestamp should be in seconds
– nischayn22
Aug 10 '16 at 14:32
add a comment |
Use this formula and set formatting to the desired time format:
=(((COLUMN_ID_HERE/60)/60)/24)+DATE(1970,1,1)
Source: http://www.bajb.net/2010/05/excel-timestamp-to-date/
Tested in libreoffice
answered Apr 17 '13 at 22:07
NeplatnyUdajNeplatnyUdaj
3,27832965
Use this formula and set formatting to the desired time format:
=(((COLUMN_ID_HERE/60)/60)/24)+DATE(1970,1,1)
Source: http://www.bajb.net/2010/05/excel-timestamp-to-date/
Tested in libreoffice
answered Apr 17 '13 at 22:07
NeplatnyUdajNeplatnyUdaj
3,27832965
answered Apr 17 '13 at 22:07
NeplatnyUdajNeplatnyUdaj
3,27832965
answered Apr 17 '13 at 22:07
NeplatnyUdajNeplatnyUdaj
3,27832965
answered Apr 17 '13 at 22:07
NeplatnyUdajNeplatnyUdaj
3,27832965
3,27832965
2
=INT(((COLUMN_ID_HERE/60)/60)/24)+DATE(1970,1,1) This works for me.. Also Note that the Timestamp should be in seconds
– nischayn22
Aug 10 '16 at 14:32
add a comment |
2
=INT(((COLUMN_ID_HERE/60)/60)/24)+DATE(1970,1,1) This works for me.. Also Note that the Timestamp should be in seconds
– nischayn22
Aug 10 '16 at 14:32
2
2
=INT(((COLUMN_ID_HERE/60)/60)/24)+DATE(1970,1,1) This works for me.. Also Note that the Timestamp should be in seconds
– nischayn22
Aug 10 '16 at 14:32
=INT(((COLUMN_ID_HERE/60)/60)/24)+DATE(1970,1,1) This works for me.. Also Note that the Timestamp should be in seconds
– nischayn22
Aug 10 '16 at 14:32
add a comment |
If you get a Error 509 in Libre office you may replace , by ; in the DATE() function
=(((COLUMN_ID_HERE/60)/60)/24)+DATE(1970;1;1)
answered Apr 24 '14 at 20:36
Karl AdlerKarl Adler
6,88934262
5
This may depend on the locales settings in your OS.
– lorenz
Oct 31 '14 at 10:21
add a comment |
If you get a Error 509 in Libre office you may replace , by ; in the DATE() function
=(((COLUMN_ID_HERE/60)/60)/24)+DATE(1970;1;1)
answered Apr 24 '14 at 20:36
Karl AdlerKarl Adler
6,88934262
5
This may depend on the locales settings in your OS.
– lorenz
Oct 31 '14 at 10:21
add a comment |
If you get a Error 509 in Libre office you may replace , by ; in the DATE() function
=(((COLUMN_ID_HERE/60)/60)/24)+DATE(1970;1;1)
answered Apr 24 '14 at 20:36
Karl AdlerKarl Adler
6,88934262
If you get a Error 509 in Libre office you may replace , by ; in the DATE() function
=(((COLUMN_ID_HERE/60)/60)/24)+DATE(1970;1;1)
answered Apr 24 '14 at 20:36
Karl AdlerKarl Adler
6,88934262
answered Apr 24 '14 at 20:36
Karl AdlerKarl Adler
6,88934262
answered Apr 24 '14 at 20:36
Karl AdlerKarl Adler
6,88934262
answered Apr 24 '14 at 20:36
Karl AdlerKarl Adler
6,88934262
6,88934262
5
This may depend on the locales settings in your OS.
– lorenz
Oct 31 '14 at 10:21
add a comment |
5
This may depend on the locales settings in your OS.
– lorenz
Oct 31 '14 at 10:21
5
5
This may depend on the locales settings in your OS.
– lorenz
Oct 31 '14 at 10:21
This may depend on the locales settings in your OS.
– lorenz
Oct 31 '14 at 10:21
add a comment |
If your file is really big try to use following formula:
=A1 / 86400 + 25569
A1 should be replaced to what your need.
Should work faster than =(((COLUMN_ID_HERE/60)/60)/24)+DATE(1970,1,1) cause of less number of calculations needed.
answered Feb 22 '14 at 17:29
Alexey MatskoffAlexey Matskoff
35426
This is a more elegant solution and also keeps the 'time' part of the timestamp (not just the date)
– Patrick
May 28 '17 at 3:14
add a comment |
If your file is really big try to use following formula:
=A1 / 86400 + 25569
A1 should be replaced to what your need.
Should work faster than =(((COLUMN_ID_HERE/60)/60)/24)+DATE(1970,1,1) cause of less number of calculations needed.
answered Feb 22 '14 at 17:29
Alexey MatskoffAlexey Matskoff
35426
This is a more elegant solution and also keeps the 'time' part of the timestamp (not just the date)
– Patrick
May 28 '17 at 3:14
add a comment |
If your file is really big try to use following formula:
=A1 / 86400 + 25569
A1 should be replaced to what your need.
Should work faster than =(((COLUMN_ID_HERE/60)/60)/24)+DATE(1970,1,1) cause of less number of calculations needed.
answered Feb 22 '14 at 17:29
Alexey MatskoffAlexey Matskoff
35426
If your file is really big try to use following formula:
=A1 / 86400 + 25569
A1 should be replaced to what your need.
Should work faster than =(((COLUMN_ID_HERE/60)/60)/24)+DATE(1970,1,1) cause of less number of calculations needed.
answered Feb 22 '14 at 17:29
Alexey MatskoffAlexey Matskoff
35426
edited Jan 21 '15 at 16:21
answered Feb 22 '14 at 17:29
Alexey MatskoffAlexey Matskoff
35426
answered Feb 22 '14 at 17:29
Alexey MatskoffAlexey Matskoff
35426
answered Feb 22 '14 at 17:29
Alexey MatskoffAlexey Matskoff
35426
35426
This is a more elegant solution and also keeps the 'time' part of the timestamp (not just the date)
– Patrick
May 28 '17 at 3:14
add a comment |
This is a more elegant solution and also keeps the 'time' part of the timestamp (not just the date)
– Patrick
May 28 '17 at 3:14
This is a more elegant solution and also keeps the 'time' part of the timestamp (not just the date)
– Patrick
May 28 '17 at 3:14
This is a more elegant solution and also keeps the 'time' part of the timestamp (not just the date)
– Patrick
May 28 '17 at 3:14
add a comment |
=(((A1/60)/60)/24)+DATE(1970,1,1)+(-5/24)
assuming A1 is the cell where your time stamp is located and dont forget to adjust to account for the time zone you are in (5 assuming you are on EST)
answered Apr 15 '14 at 19:51
LanaLana
7111
add a comment |
=(((A1/60)/60)/24)+DATE(1970,1,1)+(-5/24)
assuming A1 is the cell where your time stamp is located and dont forget to adjust to account for the time zone you are in (5 assuming you are on EST)
answered Apr 15 '14 at 19:51
LanaLana
7111
add a comment |
=(((A1/60)/60)/24)+DATE(1970,1,1)+(-5/24)
assuming A1 is the cell where your time stamp is located and dont forget to adjust to account for the time zone you are in (5 assuming you are on EST)
answered Apr 15 '14 at 19:51
LanaLana
7111
=(((A1/60)/60)/24)+DATE(1970,1,1)+(-5/24)
assuming A1 is the cell where your time stamp is located and dont forget to adjust to account for the time zone you are in (5 assuming you are on EST)
answered Apr 15 '14 at 19:51
LanaLana
7111
edited Apr 15 '14 at 20:16
user1907906
answered Apr 15 '14 at 19:51
LanaLana
7111
answered Apr 15 '14 at 19:51
LanaLana
7111
answered Apr 15 '14 at 19:51
LanaLana
7111
7111
add a comment |
add a comment |
This DATE-thing won't work in all Excel-versions.
=CELL_ID/(60 * 60 * 24) + "1/1/1970"
is a save bet instead.
The quotes are necessary to prevent Excel from calculating the term.
answered Sep 19 '16 at 6:21
Stephan WeinholdStephan Weinhold
1,25911732
add a comment |
This DATE-thing won't work in all Excel-versions.
=CELL_ID/(60 * 60 * 24) + "1/1/1970"
is a save bet instead.
The quotes are necessary to prevent Excel from calculating the term.
answered Sep 19 '16 at 6:21
Stephan WeinholdStephan Weinhold
1,25911732
add a comment |
This DATE-thing won't work in all Excel-versions.
=CELL_ID/(60 * 60 * 24) + "1/1/1970"
is a save bet instead.
The quotes are necessary to prevent Excel from calculating the term.
answered Sep 19 '16 at 6:21
Stephan WeinholdStephan Weinhold
1,25911732
This DATE-thing won't work in all Excel-versions.
=CELL_ID/(60 * 60 * 24) + "1/1/1970"
is a save bet instead.
The quotes are necessary to prevent Excel from calculating the term.
answered Sep 19 '16 at 6:21
Stephan WeinholdStephan Weinhold
1,25911732
answered Sep 19 '16 at 6:21
Stephan WeinholdStephan Weinhold
1,25911732
answered Sep 19 '16 at 6:21
Stephan WeinholdStephan Weinhold
1,25911732
answered Sep 19 '16 at 6:21
Stephan WeinholdStephan Weinhold
1,25911732
1,25911732
add a comment |
add a comment |
A timestamp is the elapsed time since Epoch time (01/01/1970), so basically we have to convert this time in days, and add the epoch time, to get a valid format for any Excel like spreadsheet software.
From a timestamp in milliseconds (ex: 1488380243994)
use this formula:
=A1/1000/86400+25569
with this formater:
yyyy-mm-dd hh:mm:ss.000
From a timestamp in seconds (ex: 1488380243)
use this formula:
=A1/86400+25569
with this formater:
yyyy-mm-dd hh:mm:ss
Where A1 is your column identifier.
Given custom formaters allow to not loose precision in displayed data, but you can of course use any other date/time one that corresponds to your needs.
answered Oct 18 '18 at 14:21
DonatelloDonatello
1,73621521
add a comment |
A timestamp is the elapsed time since Epoch time (01/01/1970), so basically we have to convert this time in days, and add the epoch time, to get a valid format for any Excel like spreadsheet software.
From a timestamp in milliseconds (ex: 1488380243994)
use this formula:
=A1/1000/86400+25569
with this formater:
yyyy-mm-dd hh:mm:ss.000
From a timestamp in seconds (ex: 1488380243)
use this formula:
=A1/86400+25569
with this formater:
yyyy-mm-dd hh:mm:ss
Where A1 is your column identifier.
Given custom formaters allow to not loose precision in displayed data, but you can of course use any other date/time one that corresponds to your needs.
answered Oct 18 '18 at 14:21
DonatelloDonatello
1,73621521
add a comment |
A timestamp is the elapsed time since Epoch time (01/01/1970), so basically we have to convert this time in days, and add the epoch time, to get a valid format for any Excel like spreadsheet software.
From a timestamp in milliseconds (ex: 1488380243994)
use this formula:
=A1/1000/86400+25569
with this formater:
yyyy-mm-dd hh:mm:ss.000
From a timestamp in seconds (ex: 1488380243)
use this formula:
=A1/86400+25569
with this formater:
yyyy-mm-dd hh:mm:ss
Where A1 is your column identifier.
Given custom formaters allow to not loose precision in displayed data, but you can of course use any other date/time one that corresponds to your needs.
answered Oct 18 '18 at 14:21
DonatelloDonatello
1,73621521
A timestamp is the elapsed time since Epoch time (01/01/1970), so basically we have to convert this time in days, and add the epoch time, to get a valid format for any Excel like spreadsheet software.
From a timestamp in milliseconds (ex: 1488380243994)
use this formula:
=A1/1000/86400+25569
with this formater:
yyyy-mm-dd hh:mm:ss.000
From a timestamp in seconds (ex: 1488380243)
use this formula:
=A1/86400+25569
with this formater:
yyyy-mm-dd hh:mm:ss
Where A1 is your column identifier.
Given custom formaters allow to not loose precision in displayed data, but you can of course use any other date/time one that corresponds to your needs.
answered Oct 18 '18 at 14:21
DonatelloDonatello
1,73621521
answered Oct 18 '18 at 14:21
DonatelloDonatello
1,73621521
answered Oct 18 '18 at 14:21
DonatelloDonatello
1,73621521
answered Oct 18 '18 at 14:21
DonatelloDonatello
1,73621521
1,73621521
add a comment |
add a comment |
below formula worked form me in MS EXEL
=TEXT(CELL_VALUE/24/60/60/1000 + 25569,"YYYY-MM-DD HH:MM")
CELL_VALUE is timestamp in milliseconds
here is explanation for text function.
answered Nov 22 '18 at 10:31
ranjeetcaoranjeetcao
92621120
Could you add some explanation to the code?
– Federico Grandi
Nov 22 '18 at 18:11
add a comment |
below formula worked form me in MS EXEL
=TEXT(CELL_VALUE/24/60/60/1000 + 25569,"YYYY-MM-DD HH:MM")
CELL_VALUE is timestamp in milliseconds
here is explanation for text function.
answered Nov 22 '18 at 10:31
ranjeetcaoranjeetcao
92621120
Could you add some explanation to the code?
– Federico Grandi
Nov 22 '18 at 18:11
add a comment |
below formula worked form me in MS EXEL
=TEXT(CELL_VALUE/24/60/60/1000 + 25569,"YYYY-MM-DD HH:MM")
CELL_VALUE is timestamp in milliseconds
here is explanation for text function.
answered Nov 22 '18 at 10:31
ranjeetcaoranjeetcao
92621120
below formula worked form me in MS EXEL
=TEXT(CELL_VALUE/24/60/60/1000 + 25569,"YYYY-MM-DD HH:MM")
CELL_VALUE is timestamp in milliseconds
here is explanation for text function.
answered Nov 22 '18 at 10:31
ranjeetcaoranjeetcao
92621120
edited Nov 24 '18 at 16:03
answered Nov 22 '18 at 10:31
ranjeetcaoranjeetcao
92621120
answered Nov 22 '18 at 10:31
ranjeetcaoranjeetcao
92621120
answered Nov 22 '18 at 10:31
ranjeetcaoranjeetcao
92621120
92621120
Could you add some explanation to the code?
– Federico Grandi
Nov 22 '18 at 18:11
add a comment |
Could you add some explanation to the code?
– Federico Grandi
Nov 22 '18 at 18:11
Could you add some explanation to the code?
– Federico Grandi
Nov 22 '18 at 18:11
Could you add some explanation to the code?
– Federico Grandi
Nov 22 '18 at 18:11
add a comment |
AD ticks to datetime format: =A1/864000000000 - 109205
answered Dec 15 '15 at 13:20
Evgeniy NikulovEvgeniy Nikulov
9
add a comment |
AD ticks to datetime format: =A1/864000000000 - 109205
answered Dec 15 '15 at 13:20
Evgeniy NikulovEvgeniy Nikulov
9
add a comment |
AD ticks to datetime format: =A1/864000000000 - 109205
answered Dec 15 '15 at 13:20
Evgeniy NikulovEvgeniy Nikulov
9
AD ticks to datetime format: =A1/864000000000 - 109205
answered Dec 15 '15 at 13:20
Evgeniy NikulovEvgeniy Nikulov
9
answered Dec 15 '15 at 13:20
Evgeniy NikulovEvgeniy Nikulov
9
answered Dec 15 '15 at 13:20
Evgeniy NikulovEvgeniy Nikulov
9
answered Dec 15 '15 at 13:20
Evgeniy NikulovEvgeniy Nikulov
9
9
add a comment |
add a comment |
i got result from this in LibreOffice Calc :
=DATE(1970,1,1)+Column_id_here/60/60/24
answered Jan 31 '16 at 7:30
Samir SayyadSamir Sayyad
112
add a comment |
i got result from this in LibreOffice Calc :
=DATE(1970,1,1)+Column_id_here/60/60/24
answered Jan 31 '16 at 7:30
Samir SayyadSamir Sayyad
112
add a comment |
i got result from this in LibreOffice Calc :
=DATE(1970,1,1)+Column_id_here/60/60/24
answered Jan 31 '16 at 7:30
Samir SayyadSamir Sayyad
112
i got result from this in LibreOffice Calc :
=DATE(1970,1,1)+Column_id_here/60/60/24
answered Jan 31 '16 at 7:30
Samir SayyadSamir Sayyad
112
answered Jan 31 '16 at 7:30
Samir SayyadSamir Sayyad
112
answered Jan 31 '16 at 7:30
Samir SayyadSamir Sayyad
112
answered Jan 31 '16 at 7:30
Samir SayyadSamir Sayyad
112
112
add a comment |
add a comment |
This worked for me:
=(col_name]/60/60/24)+(col_name-1)
edited Mar 3 '17 at 10:45
deblocker
5,12721334
answered Mar 3 '17 at 8:28
Aparna KamathAparna Kamath
1
add a comment |
This worked for me:
=(col_name]/60/60/24)+(col_name-1)
edited Mar 3 '17 at 10:45
deblocker
5,12721334
answered Mar 3 '17 at 8:28
Aparna KamathAparna Kamath
1
add a comment |
This worked for me:
=(col_name]/60/60/24)+(col_name-1)
edited Mar 3 '17 at 10:45
deblocker
5,12721334
answered Mar 3 '17 at 8:28
Aparna KamathAparna Kamath
1
This worked for me:
=(col_name]/60/60/24)+(col_name-1)
edited Mar 3 '17 at 10:45
deblocker
5,12721334
answered Mar 3 '17 at 8:28
Aparna KamathAparna Kamath
1
edited Mar 3 '17 at 10:45
deblocker
5,12721334
edited Mar 3 '17 at 10:45
deblocker
5,12721334
edited Mar 3 '17 at 10:45
deblocker
5,12721334
5,12721334
answered Mar 3 '17 at 8:28
Aparna KamathAparna Kamath
1
answered Mar 3 '17 at 8:28
Aparna KamathAparna Kamath
1
answered Mar 3 '17 at 8:28
Aparna KamathAparna Kamath
1
1
add a comment |
add a comment |
The answer of @NeplatnyUdaj is right but consider that Excel want the function name in the set language, in my case German. Then you need to use "DATUM" instead of "DATE":
=(((COLUMN_ID_HERE/60)/60)/24)+DATUM(1970,1,1)
answered Oct 18 '18 at 14:44
ownkingownking
1,40311826
add a comment |
The answer of @NeplatnyUdaj is right but consider that Excel want the function name in the set language, in my case German. Then you need to use "DATUM" instead of "DATE":
=(((COLUMN_ID_HERE/60)/60)/24)+DATUM(1970,1,1)
answered Oct 18 '18 at 14:44
ownkingownking
1,40311826
add a comment |
The answer of @NeplatnyUdaj is right but consider that Excel want the function name in the set language, in my case German. Then you need to use "DATUM" instead of "DATE":
=(((COLUMN_ID_HERE/60)/60)/24)+DATUM(1970,1,1)
answered Oct 18 '18 at 14:44
ownkingownking
1,40311826
The answer of @NeplatnyUdaj is right but consider that Excel want the function name in the set language, in my case German. Then you need to use "DATUM" instead of "DATE":
=(((COLUMN_ID_HERE/60)/60)/24)+DATUM(1970,1,1)
answered Oct 18 '18 at 14:44
ownkingownking
1,40311826
answered Oct 18 '18 at 14:44
ownkingownking
1,40311826
answered Oct 18 '18 at 14:44
ownkingownking
1,40311826
answered Oct 18 '18 at 14:44
ownkingownking
1,40311826
1,40311826
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- 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%2fstackoverflow.com%2fquestions%2f16070868%2fconverting-time-stamps-in-excel-to-dates%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
1
What kind of a timestamp is that? Milliseconds? Internet is full of answers. Did you try some of those? bajb.net/2010/05/excel-timestamp-to-date
– NeplatnyUdaj
Apr 17 '13 at 21:51
The time stamp looks like this 1234817823. When I use this website (onlineconversion.com/unix_time.htm) it says Mon, 16 Feb 2009 20:57:03 GMT. My problem is how to create a function that will apply to 1 million records.
– user10165
Apr 17 '13 at 22:02
1
Then have a look at the link I've posted. I don't have excel, but I've tried that in Libreoffice Calc and the output is: 02/16/09 08:57 PM
– NeplatnyUdaj
Apr 17 '13 at 22:05
Up it works thank you soo much!!! :)
– user10165
Apr 17 '13 at 22:09