У меня есть документы Excel, содержащие временные метки эпохи Unix из приложения Java. Я хотел бы видеть, что они переводили и представляли их как удобные для чтения даты внутри Excel.
Например, следующий длинный: 1362161251894 должен оценивать что-то читаемое: 01 марта 2013 г. 11: 07: 31,894
Я предполагаю, что могу создать формулу для этого, но я не уверен, как это сделать. Спасибо!
Да, вы можете создать формулу для этого. Java и Unix/Linux подсчитывают количество миллисекунд с 1 января 1970 года, в то время как Microsoft Excel запускает это с 1 января 1900 года для Windows и 1/1/1904 для Mac OS X. Вам просто нужно сделать следующее: конвертировать:
Время по Гринвичу в Windows
=((x/1000)/86400)+(DATEVALUE("1-1-1970") - DATEVALUE("1-1-1900"))
Время по Гринвичу в Mac OS X
=((x/1000)/86400)+(DATEVALUE("1-1-1970") - DATEVALUE("1-1-1904"))
Для локального времени в Windows (замените t на текущее смещение от GMT)
=(((x/1000)-(t*3600))/86400)+(DATEVALUE("1-1-1970") - DATEVALUE("1-1-1900"))
Для локального времени в Mac OS X (замените t текущим смещением от GMT)
=(((x/1000)-(t*3600))/86400)+(DATEVALUE("1-1-1970") - DATEVALUE("1-1-1904"))
В вашем конкретном случае похоже, что вы находитесь в горном времени (смещение по часовой стрелке 7). Поэтому, если я вставляю ваше значение в 1362161251894 в новую таблицу Excel в ячейке A1, а затем вставляю следующую формулу, я получаю результат 41333.46356, который, если я затем скажу Excel в формате Date ( нажмите ctrl + 1 на ячейке): 2/28/13 11:07 AM
=(((A1/1000)-(7*3600))/86400)+(DATEVALUE("1-1-1970") - DATEVALUE("1-1-1900"))
Здесь, как нам кажется, будет дан ответ,
= (А1/86400) +25569
Excel выражает дни как целые значения и время как дробные значения. Поэтому, если время эпохи было предоставлено в миллисекундах начиная с 1-1-1970 из UNIX, мы хотим разделить на количество миллисекунд в год и добавить Excel в 1-1-1970, чтобы обеспечить читаемое время UTC для человека. Если ваше значение находится в ячейке A1, то Excel потребуется:
=A1/86400/1000+DATEVALUE("1-1-1970")
Заметьте, что вы можете сбросить /1000, если время вашей эпохи было в секундах, а не в миллисекундах. Чтобы преобразовать в локальное время, где 't' - ваше местное смещение UTC (не забудьте использовать отрицательное значение, если у вас есть отрицательное смещение UTC), вы можете добавить/вычесть смещение UTC:
=A1/86400/1000+DATEVALUE("1-1-1970")+t/24
Обратите внимание, что ваше смещение по UTC может варьироваться в зависимости от того, наблюдается ли летнее время в вашем местоположении, что делает это неполным решением для отображения локальных мест в течение года в одной электронной таблице.