Как в excel перевести текст во время

Обновлено: 23.01.2025

В программе Excel по умолчанию предусмотрены функции для работы с датой и временем: ЧАС, МИНУТЫ и СЕКУНДЫ. Детально рассмотрим эти три функции в действии на конкретных примерах. Как, когда и где их можно эффективно применять, составляя различные формулы из этих функций для работы с временем.

Примеры использования функций ЧАС, МИНУТЫ и СЕКУНДЫ для расчетов в Excel

Функция ЧАС в Excel предназначена для определения значения часа из переданного времени в качестве параметра и возвращает данные из диапазона числовых значений от 0 до 23 в зависимости от формата временной записи.

Функция МИНУТЫ в Excel используется для получения минут из переданных данных, характеризующих время, и возвращает данные из диапазона числовых значений от 0 до 59.

Функция СЕКУНДЫ в Excel применяется для получения значения секунд из данных в формате времени и возвращает числовые значения из диапазона от 0 до 59.

Контроль по часам суточного времени в Excel используя функцию ЧАС

Пример 1. Получить текущее время, определить, сколько часов прошло с начала текущих суток, сколько часов осталось до начала новых суток.

Определим текущий момент в коде времени Excel:

Вычислим количество часов от начала суток:

Определим количество часов до окончания суток:

24 – количество часов в сутках;
B3 – текущее время в часах, выраженное в виде числового значения.

Примечание: пример демонстрирует то, что результатом работы функции ЧАС является число, над которым можно выполнять любые арифметические действия.

Преобразование чисел в формат времени с помощью функций ЧАС и МИНУТЫ

Пример 2. Из приложения были подгружены моменты прохождения некоторых событий, которые были распознаны Excel в виде обычных чисел (например, 13:05 было распознано как число 1305). Необходимо преобразовать полученные значения в формат времени, выделить часы и минуты.

Исходная таблица данных:

Для преобразования данных используем функцию:

B3 – значение, распознанное Excel в качестве обычного числа;
"00\:00" – формат времени.

В результате получим:

С помощью функций ЧАС и МИНУТЫ выделим искомые величины. Аналогично определим требуемые значения для остальных событий:

Пример использования функции СЕКУНДЫ в Excel

Пример 3. Рабочий день начинается в 8:00 утра. Один работник систематически опаздывал на протяжении предыдущих 10 рабочих дней на несколько секунд. Определить общее время опозданий работника.

Внесем данные в таблицу:

Определим опоздание в секундах. Где B3 – данные о времени прихода на работу в первый день. Аналогично определим секунды опозданий для последующих дней:

Определим общее количество секунд опозданий:

Где C3:C12 – массив ячеек, содержащих значения секунд опозданий. Определим целое значение минут опозданий, зная, что в 1 мин = 60 секунд. В результате получим:

То есть, общее опоздание сотрудника за 10 дней составило 263 секунды, что более 4-х минут.

Особенности синтаксиса функций ЧАС, МИНУТЫ и СЕКУНДЫ в Excel

Функция ЧАС имеет следующую синтаксическую запись:

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

Функция МИНУТЫ имеет следующий синтаксис:

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

Синтаксис функции СЕКУНДЫ в Excel:

время_в_числовом_формате – единственный аргумент, представленный в виде данных, из которых будут вычислены значения секунд (обязателен для заполнения).

Например, у вас есть список текстовых строк на листе, и теперь вам нужно преобразовать эти текстовые строки во время или дату и время, как вы можете решить эту проблему? Здесь я расскажу вам о некоторых приемах, позволяющих быстро преобразовать текстовую строку во время в Excel.

Преобразовать текстовую строку во время

Чтобы преобразовать текстовую строку во время, вы можете использовать некоторые формулы для решения.

1. Выберите ячейку и введите эту формулу. = ВРЕМЯЗНАЧ (LEFT (A1; LEN (A1) -2) & ":" & RIGHT (A1,2)) (A1 - это текстовая строка, которую нужно преобразовать во время) в нее и нажмите Enter. При необходимости вы можете перетащить маркер заполнения в нужный диапазон. Смотрите скриншот:

2. Затем щелкните правой кнопкой мыши эти выбранные ячейки и выберите Формат ячеек из контекстного меню, а затем в Формат ячеек диалоговое окно, выберите Время из Категория список под Число на вкладке, затем выберите нужный тип времени.

3. Нажмите OK, теперь текстовые строки конвертируются во время.

Примечание:

1. Эта формула не может работать правильно, если текстовая строка содержит секунды, например 120158.

2. Если формат вашей текстовой строки - 120456P, вы можете использовать эту формулу =TEXT(--(LEFT(A1,LEN(A1)-1)),"0\:00\:00")+((RIGHT(A1,1)="P")/2) , затем отформатируйте ячейки как нужные 12-часовые часы. Смотрите скриншот:

Преобразование текстовой строки в дату и время

Если ваша текстовая строка включает дату и время, например 20141212 0312, вы можете использовать небольшую длинную формулу для решения.

1. В пустой ячейке введите эту формулу. =DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID(A1,10,2),RIGHT(A1,2),0) , а затем нажмите Enter key, при необходимости вы можете применить эту формулу к диапазону.

2. Затем щелкните правой кнопкой мыши выбранные ячейки и выберите Формат ячеек из контекстного меню, затем в Формат ячеек диалоговое окно, выберите Время из Категория список и выберите нужный тип в правом разделе.

3. Нажмите OK. Теперь вы можете увидеть текстовую строку, преобразованную в дату и время.

Наконечник.Если вы хотите преобразовать время в десятичные часы, минуты или секунды, попробуйте использовать Kutools for ExcelАвтора Конвертировать время как показано на следующем снимке экрана. Полная функция без ограничений в течение 30 дней, пожалуйста, скачайте и получите бесплатную пробную версию сейчас.

EXCEL хранит ВРЕМЯ в числовой форме (в дробной части числа). Например, 0,75 соответствует 18:00, 0,5 - 12:00. Если, по какой-то причине, значения ВРЕМЕНИ сохранены в десятичной форме, например, 10,5 часов, то их можно преобразовать в стандартный формат времени (часы : минуты : секунды). В статье также показано как преобразовать число 1800 в 18:00.

Если в ячейке содержится число 40638,5, то изменив соответствующим образом Формат ячеек можно его отобразить в виде 05.04.2011 12:00:00 (40638 соответствует дате 05.04.2011, а 0,5 соответствует 12 часам дня, подробнее в статье Как EXCEL хранит дату и время ). Для этого вызовите Диалоговое окно Формат ячеек, нажав CTRL+1 , выберите числовой формат Дата, затем выберите в поле Тип — формат 14.03.01 13:30. Также с помощью Формата ячеек число 40638,5 можно отобразить как 12:00:00.

Преобразование ВРЕМЕНИ из десятичного числа в стандартный формат времени (часы : минуты : секунды)

Пример1а

Если в ячейке A1 содержится значение времени (часов) в десятичной форме (10,5), то для отображения в стандартном формате времени используйте формулу =ТЕКСТ(A1/24; "ч:мм") Результат 10:30 в текстовом формате (функция =ТЕКСТ() возвращает текстовое значение). 0,5 означает долю часа , т.е. полчаса, т.е. 30 минут.

Если значение нужно в формате времени, то можно записать формулу =A1/24 . Формат ячейки, содержащий формулу, необходимо перевести в формат Время (см. файл примера ).

Другой вариант: =ВРЕМЯ(10;30;0) Аргументами функции ВРЕМЯ() могут быть только целые числа, поэтому 0,5 часа были заменены 30 минутами. Чтобы не переводить доли часов в минуты, можно написать формулу =ВРЕМЯ(A1;ОСТАТ(A1;1)*60;0) Результат 10:30 АМ.

Пример1б

Пусть в ячейке А1 находится 52,84. 52 - это часы, а 84 - доля часа. Результат нужно отобразить в виде 52 ч 50 мин (в текстовом формате). Используйте формулу =ЦЕЛОЕ(A1)&" ч "&ТЕКСТ(A1/24;"мм")&" мин" . Если нужен результат в 2-х ячейках (отдельно часы, отдельно минуты), то =ЦЕЛОЕ(A1) вернет часы, а =ЦЕЛОЕ(ОСТАТ(A1;1)*60) минуты.

В отличие от примера 1а, здесь количество часов превышает 24 (количество часов в сутках). Поэтому формула =ТЕКСТ(A1/24;"ч:мм") вернет 4:50, т.е. количество часов будет уменьшено на 48=2*24 (на максимальное количество часов кратных 24, которое меньше заданного значения часов). Следовательно, формат ВРЕМЯ использовать нельзя, т.к. он отображает количество часов от 0 до 23.

Пример2

Если целая часть числа содержит часы, а десятичная минуты, например, 10,75, то формула =ВРЕМЯ(ЦЕЛОЕ(A1);100*(A1-ЦЕЛОЕ(A1));0) переведет это число в формат времени 11:15. Этот подход отличается от рассмотренного выше Примера 1а (число 10,5), когда десятичная часть обозначала долю часа, а не минуты. Т.е. 0,5 в первом случае 30 минут, а во втором - 50 минут. Главное не запутаться. Лучше - не сохраняйте время в десятичном формате. Храните часы и минуты в разных ячейках, затем используйте функцию ВРЕМЯ() .

Пример3

Если число 145 означает 145 минут, то перевести его в формат ВРЕМЯ можно формулой =ВРЕМЯ(0;145;0) . Результат 2:25 АМ (утра), т.к. 2 (часа)*60 минут + 25 минут = 145 минут.

Преобразование 1800 в 18:00

Если вы вводили ( с целью ускорения ввода ) значения времени без двоеточия (1800 вместо 18:00), то EXCEL будет обращаться с этими значениями как с числами (т.е. при прибавлении 65, вы получите 1865, а не 19:05). Преобразуем число 1800 (в ячейке А1 ) в значение в формате времени (в шесть часов вечера 18:00) 4-мя способами.

1. Использование функции ВРЕМЯ Напишем формулу: =ВРЕМЯ(ЛЕВСИМВ(A1;ДЛСТР(A1)-2);ПРАВСИМВ(A1;2);) Если в А1 введено 427, формула вернет 04:27. Результат - значение в формате времени.

2. Использование функции ВРЕМЗНАЧ Напишем формулу: =ВРЕМЗНАЧ(ЗАМЕНИТЬ(A1;ДЛСТР(A1)-1;0;":")) . В формуле использована функция ЗАМЕНИТЬ() , которая вставляет двоеточие в нужное место, и функция ВРЕМЗНАЧ() , которая преобразует текстовое значение в формат времени ( ЗАМЕНИТЬ() возвращает в текстовом формате). Формат ячейки необходимо поменять на формат Время .

3. Преобразование числа в текст, затем в формат времени Переведем наше число 1800 в текстовый формат с помощью функции ТЕКСТ() и одновременно отформатируем его по шаблону "00\:00" (обратный слеш нужен для корректного интерпретирования двоеточия). Запишем формулу: =ТЕКСТ(A1;"00\:00") . Тем самым, из числа 1800 получим текст «18:00». Другой вариант =ТЕКСТ(A1;"ч:мм")

Затем слегка преобразуем формулу: =--ТЕКСТ(A1;"00\:00") . Применив к тексту арифметические операции (два минуса), мы запустим механизм автоформатирования: EXCEL попытается интерпретировать текст «18:00» как число, дату или время (мы как бы моделируем ситуацию, когда в пустую ячейку пользователь вручную вводит «18:00»). Единственное, что не сделает EXCEL в этом случае - это не изменит формат ячейки на Время . Сделаем это вручную.

Подробнее о пользовательских форматах дат и времени читайте в одноименной статье Пользовательский формат даты и времени .

4. Прямое преобразование в формат времени В формате Дата принято, что одним суткам соответствует число единица. Соответственно, конкретное время, это часть суток: двенадцать дня это 0,5; шесть вечера это 0,75. Число равное 1/24 (т.е. 0,041667) соответствует одному часу. Учитывая, что в сутках 24 часа, в часе 60 минут, а в сутках 1440 минут, напишем следующую формулу: =ЦЕЛОЕ(A1/100)/24+ОСТАТ(A1;100)/1440 . Далее изменяем формат ячейки на Время .

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

Вот несколько примеров ввода текста и как его следует преобразовать:

Заметьте, что иногда бывают минуты и секунды, а некоторые другие - только минуты / секунды, кроме того, иногда вы найдете минуты (множественное число), а другие - минуты (единственное число). Наконец, некоторые знаки препинания могут быть в тексте несколько раз.

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

Я пробовал разные версии TimeValue() и DateValue() и некоторые вложенные replace () все из них в формуле ячейки, но ни одна из них не работала для всех случаев.

Не могли бы вы дать мне идею или совет о том, как подойти к этой проблеме?

4 ответа

Я играл с твоим вопросом в течение дня и вижу, что другие сделали то же самое. Я восхищаюсь решением, предложенным Роном Розенфельдом с использованием FilterXML, которое я кратко рассмотрел и отбросил как слишком диковинное. Это не так. Но neitehr это опрятно. Для «аккуратного» взгляда не далее, чем UDF ниже. Назовите его с листа, например, =TextToTime(A1) и обязательно отформатируйте ячейку, в которую вы поместили это, как Time, возможно, как hh:mm:ss .

Эта функция очень универсальна. Он может переводить строки типа «5 часов 10 минут 15 секунд» или «5 часов 10 минут 15 секунд» (с запятыми) или даже «5 часов 10 минут 15 секунд». Ему нужны пробелы вокруг чисел, но они не мешают лишним пробелам или опечаткам. Это терпит неудачу на "75 минутах и 66 секундах", но это может быть решено, если это - проблема. Основным преимуществом VBA является то, что практически все можно решить, используя всего несколько дополнительных строк кода. Функции рабочего листа не имеют такой возможности.

Если вы решите использовать VBA и сохраните тот же формат в столбце A, вы можете использовать следующий код:

Ну, вы можете взять это и отредактировать в соответствии со своими потребностями:

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

Поскольку вы не включили VBA в качестве тега, вот несколько формульных решений.

Если у вас Windows Excel 2013+ с функцией FILTERXML , вы можете использовать следующее:

Если у вас нет функции FILTERXML :

Создайте именованную формулу:
- Formulas → Defined Names → Define Name → New Name → seq_99 → Refers to: → =IF(ROW(INDEX($A:$A,1,1):INDEX($A:$A,255,1))=1,1,(ROW(INDEX($A:$A,1,1):INDEX($A:$A,255,1))-1)*99)
Затем используйте эту формулу:
- Найдите строку hour или min или sec
- Вернуть узел до каждой из этих строк
- разделить на соответствующий коэффициент, чтобы создать значение времени Excel
Примечание. Пользовательский формат столбца B: hh:mm:yy

Как отмечает @ScottCraner в комментариях, формулы можно сократить, создав формулу массива:

В некоторых более ранних версиях Excel вам может потребоваться «подтвердить» эту массив-формулу , удерживая ctrl + shift , пока нажмите enter . Если вы сделаете это правильно, Excel поместит фигурные скобки <. >вокруг формулы, как показано на панели формул

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

Преобразовать единицы времени можно несколькими способами.

Для преобразования единицы времени в другую используйте функцию ПРЕОФОРМ.

Примечание: Для преобразований, в которые включается год, следует учесть, что год составляет 365,25 дня.
Преобразование стандартного формата времени в десятичной формат
Для преобразования из формата часы : минуты : секунды используйте функцию INT.

Преобразование десятичных форматов в стандартный формат времени
Чтобы преобразовать десятичной формат в часы: минуты : секунды, используйте оператор делетеля (/) вместе с функцией ТЕКСТ.

Примечание: Формат времени также можно применить без указания формата с помощью функции ТЕКСТ. Чтобы просмотреть число как время, выйдите из ячейки и на вкладке Главная в группе Число щелкните стрелку рядом с полем Числовом формате. Затем выберите Другие числовые форматы, в списке Категория выберите другой, а затем в поле Тип выберите пользовательский формат. Имейте в виду, что если вы используете как формат, примененный к функции ТЕКСТ, так и числовом формате, функция ТЕКСТ имеет приоритет.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Читайте также: