Я хочу ввести время без двоеточий как hhmmss и рассчитать разницу во времени с другой ячейкой. Например, я ввожу время начала в одной ячейке и время окончания в другой. Тогда мне нужно рассчитать разницу во времени. Но это должно включать часы, минуты и секунды.
Сложность в том, что я хочу ввести время без двоеточия, но увидеть его с двоеточием в клетке. Для этого я отформатировал ячейки в произвольном формате чисел 00\:00\:00 .
Одним из вариантов будет использование комбинации форматирования ячеек с функциями ВРЕМЕНИ 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:
РЕДАКТИРОВАТЬ: видя, что требование не совсем так, как указано в вопросе, я изменил формулу, чтобы учесть ведущие нули, добавив его:
=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 .
Вот еще один способ обработки вашего времени так, как вы хотите его ввести:
Я оставил время начала и окончания как неформатированные числа, чтобы сделать действие более заметным. В этом примере ваше начальное время составляет 25 секунд после полуночи или полудня, которое вы вводите как 000025. Как число, оно будет сохранено как 25, даже если ваше форматирование будет выглядеть как 00:00:25.
Этот подход отслаивает часы и минуты, основываясь на их положении в количестве, равном 100. Секунды всегда правильные две цифры, независимо от. Формула в С2:
= 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. Это дает вам остаток после деления.
Я знаю, что это старый пост, но если (как я это сделал) люди ищут решения через бесчисленные форумы, чтобы сэкономить время, вводя в табели, и не нужно вручную вводить "двоеточие", но иметь двоеточие видимым, а затем быть способен выполнять вычисления ..... все без использования VBA Script, скрытых ячеек и формул .... и т.д .... и т.д .... Это решение, которое я придумал, - и похоже, что оно работает с ведущими нулями, 24-часовым временем и расписаниями, охватывающими полночь (хотя потребуется корректировка, если вы ожидаете, что расписание будет охватывать несколько полуночей - я не видел в этом необходимости) мое дело).
Кроме того, мне не нужны "секунды", но вы сможете достаточно легко их расширить, если будете следовать концепции:
Пользовательский Формат ваших начальных и конечных ячеек:
0 # ":" ##
Это превращает их в "текстовые ячейки", но выводит цифры, чтобы всегда показывать четыре цифры (даже с ведущей нулей или нулями для американских друзей).
Обратите внимание, что это позволит вводить ЛЮБОЕ четырехзначное число в формате времени. Так что теоретически можно войти в 0768 году, и это стало временем 07:68, которого, конечно, не существует. Так что либо
а) создайте список проверки данных следующим образом:- на отдельном рабочем листе (вкладке) в одном столбце создайте список всех допустимых значений. В моем случае это были 0500, 0515, 0530, 0545 ..... 0445, поскольку я имел дело только с 15-минутными приращениями. Если вам нужно детализировать до секунд (серьезно?) это будет довольно долго ..... т.е. 050001, 050002 .... и т.д. Или вы можете написать другую формулу просто для создания этого списка, а затем скопировать текстовые данные (только для вставки значений) в новый столбец, чтобы удалить формулы (я бы). - Выберите все эти ячейки с "допустимыми значениями" и на вкладке "Forumulas" панели инструментов выберите "Define Name". Дайте ему имя типа "MonkeyTimeList". - Вернитесь к своему рабочему листу, выберите все ячейки, охватывающие время начала и окончания, и перейдите на вкладку "Данные" панели инструментов, выберите «Проверка данных> Проверка данных> Настройки», в разделе "Разрешить" выберите « List "из выпадающего списка, в разделе" Source "введите имя, которое вы определили, перед которым стоит" = ", поэтому в моем случае" = MonkeyTimeList ". Так как вы нажимаете "ОК", выбираете вкладку "Предупреждение об ошибке", ставите флажок «Показать предупреждение об ошибке ...» и в раскрывающемся списке "Стиль" выбираете "Стоп". Не стесняйтесь добавлять сообщение об ошибке типа «Человек-обезьяна говорит, что вы идиот», попробуйте ввести время в ПРАВИЛЬНОМ ФОРМАТЕ!». Затем нажмите "ОК". - Это гарантирует, что только данные из вашего "приемлемого" списка могут быть введены как время.
б) теоретически вы можете использовать условное форматирование, чтобы выделить ячейку, в которую добавлены неверные данные. Это позволит избежать использования отдельного рабочего листа для ваших данных проверки данных. Я не сделал это таким образом, поэтому я говорю теоретически. Это может быть другой учебник ....
Таким образом, теперь вы можете ввести время без необходимости искать двоеточие и клавишу Shift каждый раз.
Если вам ДЕЙСТВИТЕЛЬНО нужны секунды в формате ячейки, то пользовательский формат ячейки будет выглядеть следующим образом:
0 # ":" ## ":" ##
ТАК...... СЕЙЧАС ВЫЧИСЛИТЬ ЧАСЫ от времени начала до времени окончания ... опять же, это основано только на минутах ... но если вы можете следовать логике, то может быть расширено и до секунд.
В третьем столбце (предполагая, что столбец A - время начала, B - время окончания, а C - общее количество часов), введите формулу:
= ЕСЛИ (ЗНАЧЕНИЕ (В2)> = ЗНАЧЕНИЕ (А2), (СУММА (ЗНАЧЕНИЕ (MID (ТЕКСТ (В2, "0000"), 1,2)), ЗНАЧЕНИЕ (MID (ТЕКСТ (В2, "0000"), 3 , 2))/60))- (SUM (ЗНАЧЕНИЕ (MID (ТЕКСТ (А2, "0000"), 1,2)), ЗНАЧЕНИЕ (MID (ТЕКСТ (А2, "0000"), 3,2))/60)), (СУММА (ЗНАЧЕНИЕ (MID (ТЕКСТ (В2, "0000"), 1,2)), 24, ЗНАЧЕНИЕ (MID (ТЕКСТ (В2, "0000"), 3,2))/60))- (SUM (ЗНАЧЕНИЕ (MID (ТЕКСТ (А2, "0000"), 1,2)), ЗНАЧЕНИЕ (MID (ТЕКСТ (А2, "0000"), 3,2))/60)))
Теперь, чтобы объяснить это ...
Функция "ЕСЛИ" различает время окончания на 24-часовых часах до полуночи и после полуночи. Если после полуночи, к времени окончания добавляется 24 часа, чтобы можно было продолжить расчет времени окончания и времени начала. Если до полуночи, нет необходимости добавлять 24 часа. Таким образом, для целей расчета время 0100 фактически трактуется как 2500, 0200 - это 2600 и так далее. НОТА. это предполагает, что смена длится не более 23 часов и 45 минут. Если он работает дольше, возможно, вам нужно будет включить столбцы даты в формулу .... опять же, мне это не нужно.
Функции MID работают с TEXT (следовательно, с функцией TEXT) и возвращают цифры в определенных местах из указанной ячейки в указанном формате .... Soooo, «MID (TEXT (B2," 0000 "), 1,2)» просматривает ячейку B2 и всегда просматривает ее в формате 4 цифры / буквы и возвращает две цифры, начиная с позиции "1". В моем случае это целые часы. Другой пример: «MID (TEXT (A2," 000000 "), 5,2)» будет смотреть на ячейку A2 и возвращать две цифры, начиная с позиции "5", что может быть целыми секундами.
Я поместил функцию VALUE перед всем этим, чтобы превратить возвращаемый текст в число, чтобы мы могли работать с ним в вычислениях.
Таким образом, это позволяет нам в каждой ячейке обрабатывать часы, минуты и секунды отдельно. Расчеты с часами просты, так как они уже являются целыми числами. Однако минуты на самом деле являются долями часов или долями 60 минут, поэтому, как только вы извлечете цифры, представляющие "минуты", разделите их на "60", чтобы получить "долю" часа. Как и в случае с секундами, разделите на 3600 (количество секунд в часе), чтобы получить секунды в виде "доли часа".
Теперь, когда времена представлены в виде чисел с дробями, вы можете добавлять или вычитать, как вам нравится. В случае моей формулы, даст общее количество часов между временем начала и времени окончания. Я форматирую ячейку с формулой как Custom> 00.00, которая затем позволяет мне умножаться на почасовые ставки и т.д.
Если кто-то все еще действительно хочет секунды в своей формуле. и застрять, дайте мне знать. Если мне скучно на работе, я могу помочь .... но секунды ??? действительно???