Надежное форматирование даты и времени для расчетов

Я пытаюсь отформатировать данные даты и времени, которые мне приходят из базы данных, чтобы использовать их для расчетов.

Пример данных:
Надежное форматирование даты и времени для расчетов

В приборе, из которого извлекаются данные, операторы вводят дату и время выборки вручную, а программное обеспечение не позволяет использовать специальные символы, поэтому все они разделяются знаками «.» И «». Если вы соедините это с несоответствиями с количеством символов (например, 0 перед месяцем, днем ​​или часом) и использованием AM и PM, это приведет к тому, что мне придется повторно вводить все вручную для целей расчета, потому что все становится настолько непоследовательным.

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

Я знаю, что могу использовать функцию ЗАМЕНА, чтобы избавиться от "." И "", и если я могу преобразовать даты в строку чисел

т.е. 06.8.18 7.28 PM -> 0608181928

Я мог бы использовать комбинацию функций DATE, TIME, MOD и TRUNC для генерации даты и времени. Однако несоответствие количества символов и необходимость корректировки AM и PM - вот что меня отталкивает.

Данные с примерами уравнений:
Надежное форматирование даты и времени для расчетов

Каких результатов вы ожидаете от свиданий? Не могли бы вы включить ожидаемый результат из формулы?

BruceWayne 06.09.2018 23:24

Вам также нужна временная составляющая для ваших расчетов?

Francis 06.09.2018 23:39

@BruceWayne Я ожидаю выхода мм / дд / гг чч: мм (AM или PM), поэтому мой пример выше 0608181928 -> 18.06.18 19:28

Nathan k 06.09.2018 23:51

@Francis, время - важный компонент, но я мог бы использовать формулу = Date + Time, чтобы объединить их в отдельном столбце.

Nathan k 06.09.2018 23:54

Пробовали ли вы использовать такие инструменты, как OpenRefine, чтобы узнать, помогает ли он в процессе очистки данных перед импортом в Excel? Может быть быстрая победа

Francis 07.09.2018 00:00

Кроме того, в соответствии с вашим предыдущим комментарием, пробовали ли вы сначала разделить на отдельные компоненты даты и времени, а затем повторно объединить время после того, как вы его очистили?

Francis 07.09.2018 00:02
Преобразование HTML-таблицы в профессиональный документ Excel
Преобразование HTML-таблицы в профессиональный документ Excel
Это самый простой способ создания Excel из HTML-таблицы.
Импорт excel в laravel в базу данных
Импорт excel в laravel в базу данных
Здравствуйте, дорогой читатель, в этой статье я расскажу практическим и быстрым способом, как импортировать файл Excel в вашу базу данных с помощью...
0
6
60
2
Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Ответ принят как подходящий

Если у вашего времени есть секунды, четвертый ., тогда это сделает любое количество .:

=SUBSTITUTE(LEFT(A2,FIND(" ",A2)-1),".","/")+SUBSTITUTE(MID(A2,FIND(" ",A2)+1,99),".",":")

Затем примените желаемый формат:

Да, вы могли бы и пропустить другие, хорошая мысль. Не стесняйтесь отвечать, и я удалю свой, так как это лучше. @Биг Бен

Scott Craner 07.09.2018 00:26

это работает примерно в 90% случаев, однако я бываю в ошибках, когда между датой и временем есть дополнительный пробел или нехватка места между временем и AM / PM

Nathan k 07.09.2018 17:10

Теперь он работает для всех случаев. прежде чем запустить вашу функцию, я создаю 2 вспомогательных столбца. в первом я использую функцию TRIM для удаления дополнительных пробелов, в следующем я использую функцию if, чтобы убедиться, что между каждым AM / PM = IF (OR (RIGHT ([@ [helper 1]]], 3 ) = "PM", RIGHT ([@ [helper 1]], 3) = "AM"), [@ [helper 1]], CONCATENATE (LEFT ([@ [helper 1]]], LEN ([@ [helper 1]]) - 2), "", RIGHT ([@ [helper 1]], 2))), а затем ваша функция сделает все остальное. Спасибо большое за вашу помощь!

Nathan k 07.09.2018 17:42

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

=--SUBSTITUTE(SUBSTITUTE(A1,".",":",3),".","/")

Другие вопросы по теме