Go Back   TechArena Community > Technical Support > Computer Help > Office Help > MS Office Support
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



How to convert UTC timestamp value to date and time

MS Office Support


Reply
 
Thread Tools Search this Thread
  #1  
Old 02-02-2012
Member
 
Join Date: Jan 2012
Posts: 23
How to convert UTC timestamp value to date and time

I have downloaded 5-minute-interval stock data from a data-vendor into CSV format for display and charting in Excel. However, the date and time are in UTC timestamp value (in 10 digits) which is the number of seconds elapsed since 00:00 hours on Jan 1, 1970 UTC. I need to convert them into Excel's date and time format. Would someone help as I could not fiure out how to do it properly with Excel functions. Your help is greatly appreciated.

Reply With Quote
  #2  
Old 02-02-2012
Member
 
Join Date: Mar 2011
Posts: 540
Re: How to convert UTC timestamp value to date and time

I think it is easier to answer your question if you can have a look to the timestamp built up. The Unix timestamp is calculated yes from the seconds since 01/01/1970 notably based on GMT. Now, if the time schedule on the PC to GMT + /-x is set could be that Excel is mistaken. Can not change the computer but the time setting. In this respect, the formula would also be wrong.
Reply With Quote
  #3  
Old 02-02-2012
Member
 
Join Date: May 2011
Posts: 446
Re: How to convert UTC timestamp value to date and time

Do you trust it to M $ is not that the leap years are taken into account. A UNIX timestamp is indeed made of seconds, the timer has started on 01/01/1970 at 00:00:00 Clock. Suppose the timestamp is in cell A1, then would the formula (eg in B1) look like this: = DATE (1970, 1, 1) + (A1/24/60/60) or alternatively as follows: = DATE (1970, 1, 1) + (A1/86400). Then the desired date format (eg dd.mm.yyyy) for the cell B1, select and be happy. However, consider that the server (which runs the database) is not very likely, in our time zone (ie GMT or BST) is running, but according to GMT. This is a time shift is from +0 hours (GMT) to user time by +1 hour (GMT = winter time) or +2 hours (CEST = summer time). So you should use the admin to clarify what time zone, the timestamp was stored.
Reply With Quote
Reply

  TechArena Community > Technical Support > Computer Help > Office Help > MS Office Support
Tags: , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "How to convert UTC timestamp value to date and time"
Thread Thread Starter Forum Replies Last Post
error message: cant login This computer date/time is not synchornize with the date/time of network fazalhassan Networking & Security 2 29-11-2011 09:29 AM
Date and Time widget does not keep time correctly in Motorola Defy Sanat Portable Devices 8 19-10-2011 05:08 AM
How to convert Date and Time Formats in VB.Net Jamaima Software Development 4 08-07-2010 06:20 PM
How to convert UNIX timestamp to MySQL Warner Software Development 2 16-05-2009 08:20 PM
How to convert MYSQL into Readable PHP timestamp Aanand Software Development 2 15-05-2009 11:26 PM


All times are GMT +5.5. The time now is 06:36 PM.