How to convert UTC timestamp value to date and time

[AsceTic!]

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.

[Erakna]

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.

[GaganjyotTechie]

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.