Excel - распознавать формат даты JS

Question

У меня есть сервис, который выкладывает данные базы данных в формат CSV, который я не могу изменить прямо сейчас.

Хотя выполняющийся запрос возвращает стандартный формат даты SQL - 2014-12-26 19:41:23.012 - промежуточное программное обеспечение Node.js между ними, по-видимому, преобразует дату в стандартный формат даты Javascript:

new Date().toString();
// -> Fri Dec 26 2014 19:36:00 GMT-0800 (Pacific Standard Time)

К моему ужасу, Excel не может распознать это как формат даты, и мне приходится изо всех сил пытаться превратить его во что-то, что Excel может распознать.

Хотя я не слишком знаком с ним, я знаю, что свойство ячейки Custom формата может помочь в манипулировании датами.

Что мне нужно сделать, чтобы Excel распознал этот формат даты?

Answer 1

У меня была почти точная проблема 2 дня назад. Дата была в текстовом формате, и Excel не смог преобразовать ее без каких-либо манипуляций. Вот что я должен был сделать с вашей строкой, чтобы она заработала (с помощью Excel 2010):

1. Удалите «Пт» в начале и «GMT ...» в конце. Если текст в A1, то B1:

=MID(A1,4,LEN(A1)-35

2. Добавьте запятую между днем и годом. Итак, столбец C1:

=LEFT(B1, 7) & "," & MID(B1,8,LEN(B1))

3. Конвертировать в дату / время. Колонка D1:

=DATEVALUE(C1)+TIMEVALUE(C1)

В зависимости от того, где находятся ваши пробелы, вам, возможно, придется дурачиться с индексами строк. И вам все равно нужно будет использовать пользовательский формат, чтобы изменить отображение на то, что вы хотите.

Вы также можете объединить это в одну длинную формулу:

=DATEVALUE(LEFT(MID(A1,4,LEN(A1)-35), 7) & "," & MID(MID(A1,4,LEN(A1)-35),8,LEN(MID(A1,4,LEN(A1)-35))))+TIMEVALUE(LEFT(MID(A1,4,LEN(A1)-35), 7) & "," & MID(MID(A1,4,LEN(A1)-35),8,LEN(MID(A1,4,LEN(A1)-35))))

Answer 2

Предполагая, что ячейка A1 имеет значение «Пт 26 декабря 2014 19:36:00 GMT-0800 (стандартное тихоокеанское время)», затем используйте следующую формулу для правильного вывода даты

=DATEVALUE(MID(A1,9,2)&MID(A1,5,3)&MID(A1,12,4))

Answer 3

Пользовательский формат отображает только текст или число в нужном формате. Но базовые данные должны быть сначала распознаны Excel после того, как этот пользовательский формат будет работать.

В вашем случае вам нужно сначала получить 25 символов слева в Excel.

=LEFT(A1,25)

Затем вставьте в качестве значений и после этого примените формат даты, и Excel распознает его.