Введите время без двоеточий (ччммсс) и рассчитайте разницу во времени в Excel

Question 1

Одним из вариантов может быть использование комбинации форматирования ячеек с Excel.ВРЕМЯфункции.

Отформатируйте ячейки ввода времени (A2 и B2 в моем примере) как Text. Ожидаемый формат всегда будет hhmmss, поэтому введите начальный ноль для времени с однозначными часами. Затем вы можете выполнить расчет по этой формуле:

=TIME(LEFT(B2,2), MID(B2,3,2), RIGHT(B2,2))-TIME(LEFT(A2,2), MID(A2,3,2), RIGHT(A2,2))

Это берет 2 самых левых символа как «Часы», 2 средних символа как «Минуты» и 2 самых правых символа как «Секунды» и преобразует их в то, что Excel распознает как время. Затем он вычитает один из другого и отображает результат с форматированием hhmmss:

EDIT: Поскольку требование не совсем такое, как указано в вопросе, я изменил формулу, чтобы учесть начальные нули, удалив их:

=TIME(LEFT(RIGHT("000000"&B2,6),2), MID(RIGHT("000000"&B2,6),3,2), RIGHT(RIGHT("000000"&B2,6),2))-TIME(LEFT(RIGHT("000000"&A2,6),2), MID(RIGHT("000000"&A2,6),3,2), RIGHT(RIGHT("000000"&A2,6),2))

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

Я полагаю, что вам действительно захочется специально отформатировать результат, как hh:mm:ssв этом случае.

Answer

Одним из вариантов может быть использование комбинации форматирования ячеек с Excel.ВРЕМЯфункции.

Отформатируйте ячейки ввода времени (A2 и B2 в моем примере) как Text. Ожидаемый формат всегда будет hhmmss, поэтому введите начальный ноль для времени с однозначными часами. Затем вы можете выполнить расчет по этой формуле:

=TIME(LEFT(B2,2), MID(B2,3,2), RIGHT(B2,2))-TIME(LEFT(A2,2), MID(A2,3,2), RIGHT(A2,2))

Это берет 2 самых левых символа как «Часы», 2 средних символа как «Минуты» и 2 самых правых символа как «Секунды» и преобразует их в то, что Excel распознает как время. Затем он вычитает один из другого и отображает результат с форматированием hhmmss:

EDIT: Поскольку требование не совсем такое, как указано в вопросе, я изменил формулу, чтобы учесть начальные нули, удалив их:

=TIME(LEFT(RIGHT("000000"&B2,6),2), MID(RIGHT("000000"&B2,6),3,2), RIGHT(RIGHT("000000"&B2,6),2))-TIME(LEFT(RIGHT("000000"&A2,6),2), MID(RIGHT("000000"&A2,6),3,2), RIGHT(RIGHT("000000"&A2,6),2))

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

Я полагаю, что вам действительно захочется специально отформатировать результат, как hh:mm:ssв этом случае.

Question 2

Вот еще один способ расшифровки времени так, как вы хотите его ввести:

Я оставил начальное и конечное время как неформатированные числа, чтобы сделать действие более заметным. В этом примере ваше начальное время составляет 25 секунд после полуночи или полудня, что вы введете как 000025. Как число, оно будет сохранено как 25, хотя ваше форматирование сделает его похожим на 00:00:25.

Этот подход отделяет часы и минуты в зависимости от их положения в числе, являющемся степенями 100. Секунды всегда являются правильными двумя цифрами, независимо от этого. Формула в C2:

= TIME(INT(B2/10000), INT(MOD(B2/10000,1)*100), RIGHT(B2,2))
 -TIME(INT(A2/10000), INT(MOD(A2/10000,1)*100), RIGHT(A2,2))

Функция MOD является противоположностью функции INT. Она возвращает остаток после деления.

Answer

Вот еще один способ расшифровки времени так, как вы хотите его ввести:

Я оставил начальное и конечное время как неформатированные числа, чтобы сделать действие более заметным. В этом примере ваше начальное время составляет 25 секунд после полуночи или полудня, что вы введете как 000025. Как число, оно будет сохранено как 25, хотя ваше форматирование сделает его похожим на 00:00:25.

Этот подход отделяет часы и минуты в зависимости от их положения в числе, являющемся степенями 100. Секунды всегда являются правильными двумя цифрами, независимо от этого. Формула в C2:

= TIME(INT(B2/10000), INT(MOD(B2/10000,1)*100), RIGHT(B2,2))
 -TIME(INT(A2/10000), INT(MOD(A2/10000,1)*100), RIGHT(A2,2))

Функция MOD является противоположностью функции INT. Она возвращает остаток после деления.

Question 3

Я знаю, что это старый пост, но если (как и я) люди ищут на бесчисленных форумах решение, позволяющее сэкономить время на вводе табелей учета рабочего времени и не вводить вручную «двоеточие», но при этом сделать двоеточие видимым, а затем иметь возможность выполнять вычисления... и все это без использования скрипта VBA, скрытых ячеек и формул... и т. д. и т. п. Это решение, которое я придумал - и оно, похоже, работает с ведущими нулями, 24-часовым форматом времени и табелями учета рабочего времени, охватывающими полночь (хотя потребуется корректировка, если вы ожидаете, что табель учета рабочего времени будет охватывать несколько полуночей - я не видел в этом необходимости в моем случае).

Кроме того, мне не нужны «Секунды», но вы сможете достаточно легко их расширить, если будете следовать концепции:

Пользовательский формат ячеек времени начала и времени окончания:

0#":"##

Это превращает их в «текстовые ячейки», но дополняет цифры, чтобы всегда отображать четыре цифры (даже с начальным нулем или нулем для американских друзей).

Обратите внимание, это позволит ввести ЛЮБОЕ четырехзначное число, которое будет выглядеть как формат времени. Так что теоретически возможно ввести 0768, и это станет временем 07:68, которое, конечно, не существует. Так что либо

a) создайте список проверки данных следующим образом: - на отдельном листе (вкладке) в одном столбце создайте список всех приемлемых значений. В моем случае это были значения от 0500, 0515, 0530, 0545.....0445, поскольку я имел дело только с 15-минутными интервалами. Если вам нужно перейти к секундам (серьезно?), это будет довольно долго.....например, 050001, 050002.... и т. д. Или вы можете написать другую формулу только для создания этого списка, а затем скопировать текстовые данные (вставить только значения) в новый столбец, чтобы удалить формулы (я бы так и сделал). - Выберите все эти ячейки с «допустимыми значениями» и на вкладке «Формулы» панели инструментов выберите «Определить имя». Дайте ему имя, например «MonkeyTimeList». - Вернитесь к рабочему листу табеля учета рабочего времени, выберите все ячейки, охватывающие как начальное, так и конечное время, и перейдите на вкладку «Данные» панели инструментов, выберите «Проверка данных>Проверка данных>Настройки», в разделе «Разрешить» выберите «Список» из раскрывающегося списка, а в разделе «Источник» введите определенное вами имя с префиксом «=», так что в моем случае «=MonkeyTimeList». Сначала нажмите «ОК», выберите вкладку «Оповещение об ошибке», отметьте галочкой поле «Показывать оповещение об ошибке...», а в раскрывающемся списке «Стиль» выберите «Стоп». Не стесняйтесь добавлять сообщение об ошибке, например «Обезьяна говорит, что вы идиот, попробуйте ввести время в ПРАВИЛЬНОМ ФОРМАТЕ!». Затем нажмите «ОК». - Это гарантирует, что в качестве времени могут быть введены только данные из вашего «допустимого» списка.

б) теоретически вы могли бы использовать условное форматирование только для выделения ячейки, в которую добавлены неверные данные. Это позволило бы избежать использования отдельного листа для данных проверки данных. Я не делал этого таким образом, поэтому и говорю «теоретически». Это может быть другой учебник...

Итак, теперь вы можете вводить время, не ища каждый раз двоеточие и клавишу Shift.

Если вам ДЕЙСТВИТЕЛЬНО нужны секунды в формате ячейки, то пользовательский формат ячейки будет выглядеть так:

0#":"##":"##

ИТАК...... ТЕПЕРЬ РАССЧИТАЕМ ЧАСЫ от начального времени до конечного времени.... опять же, это основано только на минутах.... но если вы можете следовать логике, то можно расширить и до секунд.

В третьем столбце (предполагая, что столбец A — это время начала, B — время окончания, а C — общее количество часов) введите формулу:

=ЕСЛИ(ЗНАЧЕНИЕ(B2)>=ЗНАЧЕНИЕ(A2),(СУММА(ЗНАЧЕНИЕ(СРЕДНЕЕ(ТЕКСТ(B2,"0000"),1,2)),ЗНАЧЕНИЕ(СРЕДНЕЕ(ТЕКСТ(B2,"0000"),3,2))/60))-(СУММА(ЗНАЧЕНИЕ(СРЕДНЕЕ(ТЕКСТ(A2,"0000"),1,2)),ЗНАЧЕНИЕ(СРЕДНЕЕ(ТЕКСТ(A2,"0000"),3,2))/60)),(СУММА(ЗНАЧЕНИЕ(СРЕДНЕЕ(ТЕКСТ(B2,"0000"),1,2)),24,ЗНАЧЕНИЕ(СРЕДНЕЕ(ТЕКСТ(B2,"0000"),3,2))/60))-(СУММА(ЗНАЧЕНИЕ(СРЕДНЕЕ(ТЕКСТ(A2,"0000"),1,2)),ЗНАЧЕНИЕ(СРЕДНЕЕ(ТЕКСТ(A2,"0000"),3,2))/60)))

Теперь объясню...

Функция «ЕСЛИ» различает время окончания по 24-часовому формату, которое до полуночи, и время после полуночи. ЕСЛИ после полуночи, то ко времени окончания добавляется 24 часа, чтобы расчет времени окончания минус время начала все еще работал. ЕСЛИ до полуночи, то нет необходимости добавлять 24 часа. Таким образом, для целей расчета время 01:00 фактически рассматривается как 25:00, 02:00 — как 26:00 и так далее. ПРИМЕЧАНИЕ. Это предполагает, что смена длится не более 23 часов и 45 минут. Если она длится дольше, возможно, вам нужно будет учесть столбцы дат в вашей формуле.... опять же, мне это не нужно.

Функции MID работают с TEXT (отсюда и функция TEXT) и возвращают цифры в определенных местах из указанной ячейки в указанном формате.... Итак, "MID(TEXT(B2,"0000"),1,2)" просматривает ячейку B2 и всегда просматривает ее в формате 4 цифр/букв и возвращает две цифры, начиная с позиции "1". В моем случае это представляет целые часы. Другой пример: "MID(TEXT(A2,"000000"),5,2)" просматривает ячейку A2 и возвращает две цифры, начиная с позиции "5", что может быть целыми секундами.

Я поместил функцию «ЗНАЧЕНИЕ» перед всем этим, чтобы преобразовать возвращаемый текст обратно в число, чтобы мы могли работать с ним в вычислениях.

Итак, это позволяет нам для каждой ячейки обрабатывать часы, минуты и секунды отдельно. Расчеты с часами просты, поскольку они уже являются целыми числами. Однако минуты на самом деле являются долями часов или долями 60 минут, поэтому, как только вы извлечете цифры, представляющие «минуты», разделите на «60», чтобы получить «долю» часа. Аналогично с секундами, разделите на 3600 (количество секунд в часе), чтобы получить секунды как «долю часа».

Теперь, когда время представлено в виде чисел с дробями, вы можете добавлять или вычитать по своему усмотрению. В случае моей формулы, даст общее количество часов между временем начала и временем окончания. Я форматирую ячейку с формулой как Пользовательский> 00.00, что затем позволяет мне умножать на почасовые ставки и т. д.

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

Answer

Я знаю, что это старый пост, но если (как и я) люди ищут на бесчисленных форумах решение, позволяющее сэкономить время на вводе табелей учета рабочего времени и не вводить вручную «двоеточие», но при этом сделать двоеточие видимым, а затем иметь возможность выполнять вычисления... и все это без использования скрипта VBA, скрытых ячеек и формул... и т. д. и т. п. Это решение, которое я придумал - и оно, похоже, работает с ведущими нулями, 24-часовым форматом времени и табелями учета рабочего времени, охватывающими полночь (хотя потребуется корректировка, если вы ожидаете, что табель учета рабочего времени будет охватывать несколько полуночей - я не видел в этом необходимости в моем случае).

Кроме того, мне не нужны «Секунды», но вы сможете достаточно легко их расширить, если будете следовать концепции:

Пользовательский формат ячеек времени начала и времени окончания:

0#":"##

Это превращает их в «текстовые ячейки», но дополняет цифры, чтобы всегда отображать четыре цифры (даже с начальным нулем или нулем для американских друзей).

Обратите внимание, это позволит ввести ЛЮБОЕ четырехзначное число, которое будет выглядеть как формат времени. Так что теоретически возможно ввести 0768, и это станет временем 07:68, которое, конечно, не существует. Так что либо

a) создайте список проверки данных следующим образом: - на отдельном листе (вкладке) в одном столбце создайте список всех приемлемых значений. В моем случае это были значения от 0500, 0515, 0530, 0545.....0445, поскольку я имел дело только с 15-минутными интервалами. Если вам нужно перейти к секундам (серьезно?), это будет довольно долго.....например, 050001, 050002.... и т. д. Или вы можете написать другую формулу только для создания этого списка, а затем скопировать текстовые данные (вставить только значения) в новый столбец, чтобы удалить формулы (я бы так и сделал). - Выберите все эти ячейки с «допустимыми значениями» и на вкладке «Формулы» панели инструментов выберите «Определить имя». Дайте ему имя, например «MonkeyTimeList». - Вернитесь к рабочему листу табеля учета рабочего времени, выберите все ячейки, охватывающие как начальное, так и конечное время, и перейдите на вкладку «Данные» панели инструментов, выберите «Проверка данных>Проверка данных>Настройки», в разделе «Разрешить» выберите «Список» из раскрывающегося списка, а в разделе «Источник» введите определенное вами имя с префиксом «=», так что в моем случае «=MonkeyTimeList». Сначала нажмите «ОК», выберите вкладку «Оповещение об ошибке», отметьте галочкой поле «Показывать оповещение об ошибке...», а в раскрывающемся списке «Стиль» выберите «Стоп». Не стесняйтесь добавлять сообщение об ошибке, например «Обезьяна говорит, что вы идиот, попробуйте ввести время в ПРАВИЛЬНОМ ФОРМАТЕ!». Затем нажмите «ОК». - Это гарантирует, что в качестве времени могут быть введены только данные из вашего «допустимого» списка.

б) теоретически вы могли бы использовать условное форматирование только для выделения ячейки, в которую добавлены неверные данные. Это позволило бы избежать использования отдельного листа для данных проверки данных. Я не делал этого таким образом, поэтому и говорю «теоретически». Это может быть другой учебник...

Итак, теперь вы можете вводить время, не ища каждый раз двоеточие и клавишу Shift.

Если вам ДЕЙСТВИТЕЛЬНО нужны секунды в формате ячейки, то пользовательский формат ячейки будет выглядеть так:

0#":"##":"##

ИТАК...... ТЕПЕРЬ РАССЧИТАЕМ ЧАСЫ от начального времени до конечного времени.... опять же, это основано только на минутах.... но если вы можете следовать логике, то можно расширить и до секунд.

В третьем столбце (предполагая, что столбец A — это время начала, B — время окончания, а C — общее количество часов) введите формулу:

=ЕСЛИ(ЗНАЧЕНИЕ(B2)>=ЗНАЧЕНИЕ(A2),(СУММА(ЗНАЧЕНИЕ(СРЕДНЕЕ(ТЕКСТ(B2,"0000"),1,2)),ЗНАЧЕНИЕ(СРЕДНЕЕ(ТЕКСТ(B2,"0000"),3,2))/60))-(СУММА(ЗНАЧЕНИЕ(СРЕДНЕЕ(ТЕКСТ(A2,"0000"),1,2)),ЗНАЧЕНИЕ(СРЕДНЕЕ(ТЕКСТ(A2,"0000"),3,2))/60)),(СУММА(ЗНАЧЕНИЕ(СРЕДНЕЕ(ТЕКСТ(B2,"0000"),1,2)),24,ЗНАЧЕНИЕ(СРЕДНЕЕ(ТЕКСТ(B2,"0000"),3,2))/60))-(СУММА(ЗНАЧЕНИЕ(СРЕДНЕЕ(ТЕКСТ(A2,"0000"),1,2)),ЗНАЧЕНИЕ(СРЕДНЕЕ(ТЕКСТ(A2,"0000"),3,2))/60)))

Теперь объясню...

Функция «ЕСЛИ» различает время окончания по 24-часовому формату, которое до полуночи, и время после полуночи. ЕСЛИ после полуночи, то ко времени окончания добавляется 24 часа, чтобы расчет времени окончания минус время начала все еще работал. ЕСЛИ до полуночи, то нет необходимости добавлять 24 часа. Таким образом, для целей расчета время 01:00 фактически рассматривается как 25:00, 02:00 — как 26:00 и так далее. ПРИМЕЧАНИЕ. Это предполагает, что смена длится не более 23 часов и 45 минут. Если она длится дольше, возможно, вам нужно будет учесть столбцы дат в вашей формуле.... опять же, мне это не нужно.

Функции MID работают с TEXT (отсюда и функция TEXT) и возвращают цифры в определенных местах из указанной ячейки в указанном формате.... Итак, "MID(TEXT(B2,"0000"),1,2)" просматривает ячейку B2 и всегда просматривает ее в формате 4 цифр/букв и возвращает две цифры, начиная с позиции "1". В моем случае это представляет целые часы. Другой пример: "MID(TEXT(A2,"000000"),5,2)" просматривает ячейку A2 и возвращает две цифры, начиная с позиции "5", что может быть целыми секундами.

Я поместил функцию «ЗНАЧЕНИЕ» перед всем этим, чтобы преобразовать возвращаемый текст обратно в число, чтобы мы могли работать с ним в вычислениях.

Итак, это позволяет нам для каждой ячейки обрабатывать часы, минуты и секунды отдельно. Расчеты с часами просты, поскольку они уже являются целыми числами. Однако минуты на самом деле являются долями часов или долями 60 минут, поэтому, как только вы извлечете цифры, представляющие «минуты», разделите на «60», чтобы получить «долю» часа. Аналогично с секундами, разделите на 3600 (количество секунд в часе), чтобы получить секунды как «долю часа».

Теперь, когда время представлено в виде чисел с дробями, вы можете добавлять или вычитать по своему усмотрению. В случае моей формулы, даст общее количество часов между временем начала и временем окончания. Я форматирую ячейку с формулой как Пользовательский> 00.00, что затем позволяет мне умножать на почасовые ставки и т. д.

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

Введите время без двоеточий (ччммсс) и рассчитайте разницу во времени в Excel

решение1

решение2

решение3

Связанный контент