Преобразовать метку времени 00:00:00.000 в общее количество секунд и миллисекунд

Question

У меня проблема в том, что у меня осталась временная метка, которая читается как: 00: 02: 02.300, что означает: часы - минуты - секунды - миллисекунды.

и нужно отчаянно конвертировать его в общее количество секунд. Это сложно, потому что у меня есть бит миллисекунд, и в Excel нет четкого формата для этого, и нет возможности легко "обрезать" миллисекунды. В лучшем случае мне понадобится всего несколько секунд и миллисекунд, например 6528,32 или что-то в этом роде.

Answer 1

Если вы уверены, что метки времени всегда приходят в этом формате (ЧЧ: мм: сс.SSS), вы можете использовать следующую функцию Excel (при условии, что отметка времени находится в ячейке A1, и она отформатирована как текст):

=VALUE(RIGHT(A1, 6))+60*VALUE(MID(A1, 4, 2))+3600*(LEFT(A1, 2))

Для отметки времени 00:02:02.300 это даст результат 122.3 .

Если он отформатирован как дата, это еще проще:

=A1*86400

(потому что Excel хранит время как доли дня; например, 12:00:00 хранится как 0,5, потому что это половина 24 часов)

Answer 2

Если в ячейке 00:02:02.300 , она может выглядеть точно так же, если она отформатирована как текст ¹ , или может выглядеть иначе, если она отформатирована как-то еще.

Трюк легко получить за секунды и миллисекунды, то есть 122.300 .

Сначала предположим, что 00:02:02.300 находится в ячейке A1. Выделите его и отформатируйте как число. Это покажет что-то, что выглядит как чепуха. Вероятно, 0,00. Перейдите в ячейку B1 и введите =A1*86400 . Ячейка B1 теперь будет содержать 122.3 . Чтобы получить конечные нули, отформатируйте B1 как число и настройте по своему вкусу.

Чтобы правильно дать решение Глорфинделя VALUE , формула будет

=VALUE(3600*LEFT(A1,2))+VALUE(60*(MID(A1,4,2))+VALUE(MID(A1,7,2))+VALUE(MID(A1,9,3))

Я объясню, что делает эта формула. Он смотрит на ячейку A1 в виде текстовой строки, затем преобразует ее части в числа, а затем складывает их соответствующим образом, чтобы они превратились в общее количество секунд. Он состоит из четырех частей:

1. VALUE(3600*LEFT(A1,2))
2. VALUE(60*(MID(A1,4,2))
3. VALUE(MID(A1,7,2))
4. VALUE(MID(A1,9,3))

Глядя на строку 00:02:02.300 , вы можете видеть, что это в основном четыре числа, разделенные : и . , Каждое из этих четырех чисел соответствует четырем частям выше слева направо.

VALUE(3600*LEFT(A1,2)) преобразует 00 , место часов, в секунды. Пункт два работает над следующим номером 02 , переводя его в минуты. И так далее. Теперь обратите внимание в полной формуле, что каждый из пунктов в списке выше добавлен + вместе. Это дает вам секунды и десятичные дроби, 122.3 . Если вы все еще не понимаете, что делает эта формула, наберите ее медленно в Excel и обратите внимание на подсказку формулы, которая появляется автоматически. Это говорит вам точно, что делает каждая часть.

Если вы поместите все элементы в приведенном выше списке в их отдельные ячейки, вы получите:

1. 0
2. 120
3. 2
4. 0.3

Положите это вместе и что вы получаете? Биббиди Боббиди Бу! Сложите их, и это ваш ответ 0 + 120 + 2 + 0.3 = 122.3 . ²

Для дальнейшей организации ваших данных и их контроля я рекомендую скопировать все новые данные ячейки с указанием секунд и десятичных разрядов в соответствии с вашими предпочтениями, а затем вставлять значения только в новые ячейки.

---

1. Если A1 уже отформатирован как текст, оставьте его. Excel по-прежнему правильно использует его в формуле в ячейке B1. Осторожно, преобразование между форматами может навсегда испортить ваши данные. Я настоятельно рекомендую отработать копию ваших данных, а не основной файл, потому что, когда вы начинаете играть с форматами и преобразованиями текста в числа (что делает формула VALUE), есть хороший шанс, что вы случайно и навсегда измените свой данные к чему-то непригодному.
2. В этот момент вы можете спросить, зачем разделять числа 02 и 300? .300 - это доли секунды, а 02 - тоже секунды. Это одно и то же. Ну, ваши данные в будущем могут иметь своеобразный разделитель в будущем, например , или ; , Данные, которые у меня есть, разделяют их ; и конечные числа на самом деле являются кадрами видео в этом временном интервале, поэтому я должен выполнить дополнительные вычисления для получения десятичных секунд.

Answer 3

RE: вероятная причина, по которой вы получаете # ЗНАЧЕНИЕ! ошибка

• TimeValue() ожидает значение TEXT и, скорее всего, вы ссылаетесь на значение VALUE. см. https://support.office.com/en-us/article/how-to-correct-a-value-error-in-the-timevalue-function-d2a5e995-3586-4611-93a7-acfc7766dfff или Google "" Как исправить # ЗНАЧЕНИЕ! ошибка в функции TIMEVALUE ".

RE: функция Value()

• это не требуется в Excel, оно предусмотрено для совместимости с другими программами работы с электронными таблицами. см. https://support.office.com/en-us/article/how-to-correct-a-value-error-in-the-timevalue-function-d2a5e995-3586-4611-93a7-acfc7766dfff или Google "Excel ЗНАЧЕНИЕ ".

Примеры (введите и отформатируйте ячейки с использованием листа xl)

Пользовательский формат A1 в формате чч: мм: сс.000, затем введите 16: 48: 30.001

B1 общий формат в порядке, введите формулу: = TIMEVALUE(TEXT(A1, "чч: мм: сс.000"))

C1 введите формулу: = ВРЕМЯ (А1)

Результаты:

A1 16:48:30.000 B1 0.700347222 C1 # ЗНАЧЕНИЕ!

Cell Formula Комментарии:

A1: 16: 48: 30.000, просто набрано в VALUE, ячейка отформатирована как чч: мм: сс.000

B1: = TIMEVALUE(TEXT(A1, "чч: мм: сс.000")) это работает TEXT(..) - преобразование ЗНАЧЕНИЯ в A1 в TEXT в формате "чч: мм: сс.000", который Функция TIMEVALUE ожидает

C1: = TIMEVALUE(A1)- ошибки с #VALUE! поскольку A1 уже является ЗНАЧЕНИЕМ, он просто отформатирован, чтобы выглядеть как текст.

Answer 4

Отформатируйте ячейку или диапазон ячеек, как показано на снимке экрана ниже, чтобы записать миллисекунду.

• Затем введите ВРЕМЯ, я ввел текущее время с помощью =Now() .
• Вы можете использовать любую из формул для преобразования времени (с миллисекундами) в общее количество секунд.

=L13*86400 или =L13*24*60*60 вы получите 38574 .

Замечания:

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