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

В учебнике демонстрируются различные способы преобразования времени в десятичное число в Excel. Вы найдете множество формул для преобразования времени в часы, минуты или секунды, а также для преобразования текста во время и наоборот.

Поскольку Microsoft Excel использует числовую систему для хранения времени, вы можете легко преобразовать часы, минуты и секунды в числа, которые можно использовать в других вычислениях.

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

Как преобразовать время в десятичное число в Excel

Всего существует три способа преобразования значения времени в десятичное число: арифметическая операция, функция ПРЕОБРАЗОВАТЬ или комбинация трех различных функций времени.

Самый простой способ преобразовать время в десятичное число в Excel — умножить исходное значение времени на количество часов, секунд или минут в сутках:

  • Чтобы преобразовать время в количество часов, умножьте время на 24, то есть количество часов в сутках.
  • Чтобы перевести время в минуты, умножьте время на 1440, то есть количество минут в сутках (24*60).
  • Чтобы преобразовать время в секунды, умножьте время на 86400, что равно количеству секунд в сутках (24*60*60).

Преобразование времени в десятичное число в Excel

В следующих разделах вы узнаете о других методах преобразования времени в десятичное число в Excel.

Как перевести время в часы в Excel

В этом разделе демонстрируются 3 различные формулы для преобразования часов из стандартного формата времени (чч:мм:сс) в десятичное число.

Формула 1: арифметический расчет

Вы уже знаете самый быстрый способ преобразования значения времени в количество часов в Excel — умножение на 24, то есть на количество часов в одних сутках:

=A2*24 (где A2 — значение времени)

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

=ЦЕЛОЕ(A2*24)
Формула и арифметический расчет для перевода времени в часы в Excel

Формула 2: функция ПРЕОБРАЗОВАТЬ

Другой способ выполнить преобразование «время > часы» — использовать следующую формулу преобразования:

=ПРЕОБРАЗОВАТЬ(A2, «день», «час»)

Формула 3: функции ЧАС, МИНУТА и СЕКУНД

Наконец, можно использовать несколько более сложную формулу, логика которой, впрочем, вполне очевидна. Извлеките отдельные единицы времени с помощью функций HOUR, MINUTE и SECOND, затем разделите минуты на 60 (количество минут в часе) и секунды на 3600 (количество секунд в часе) и сложите результаты:

= ЧАС(A2) + МИНУТЫ(A2)/60 + СЕКУНДЫ(A2)/3600

Как перевести время в минуты в Excel

Те же три метода можно использовать для преобразования минут из стандартного формата времени в десятичное число.

Формула 1: арифметический расчет

Чтобы преобразовать время в общее количество минут, вы умножаете время на 1440, что является количеством минут в одном дне (24 часа * 60 минут = 1440):

=А2*1440

Если вы хотите вернуть количество минут соревнований, используйте функцию INT, как в предыдущем примере:

=ЦЕЛОЕ(A2*1440)

Вы можете просмотреть результаты на скриншоте ниже:
Преобразование времени в минуты в Excel

Формула 2: функция ПРЕОБРАЗОВАТЬ

Чтобы выполнить преобразование «время > минуты» с помощью функции CONVERT(number, from_unit, to_unit), укажите «день» в качестве единицы для преобразования и «mn» в качестве единицы для преобразования:

=ПРЕОБРАЗОВАТЬ(A2, «день», «мин»)

Формула 3: функции ЧАС, МИНУТА и СЕКУНД

Еще один способ получить количество минут — умножить часы на 60 и разделить секунды на то же число:

=ЧАС(A2)*60 + МИНУТЫ(A2) + СЕКУНДЫ(A2)/60

Как перевести время в секунды в Excel

Преобразование времени в общее количество секунд в Excel можно выполнить аналогичным образом.

Формула 1: арифметический расчет

Умножьте значение времени на 86400, что равно количеству секунд в сутках (24 часа * 60 минут * 60 секунд = 86400):

=А2*86400
Преобразование времени в секунды в Excel

Формула 2: функция ПРЕОБРАЗОВАТЬ

Формула в основном такая же, как и в приведенных выше примерах, с той лишь разницей, что вы конвертируете «день» в «сек»:

=ПРЕОБРАЗОВАТЬ(A2, «день», «сек»)

Формула 3: функции ЧАС, МИНУТА и СЕКУНД

Используйте функции HOUR, MINUTE и SECOND, как в двух предыдущих примерах (я полагаю, что на данный момент вам не нужны дальнейшие объяснения логики формулы 🙂

=ЧАС(A2)*3600 + МИНУТЫ(A2)*60 + СЕКУНДЫ(A2)

Советы:

  1. Если какая-либо из приведенных выше формул возвращает значение, отформатированное как время, просто измените формат ячейки на Общий, чтобы отобразить его в виде числа.
  2. Чтобы преобразовать время в десятичное число, представляющее время во внутренней системе Excel, примените Общий форматировать в ячейку. При таком подходе 23:59:59 будет преобразовано в 0,99999, 06:00 — в 0,25, а 12:00 — в 0,5. Если целая часть преобразованного числа больше нуля, это означает, что ваша ячейка содержит значения даты и времени.

Как разделить дату и время в Excel

Как это часто бывает, ваш рабочий лист Excel может содержать даты и время в одной ячейке, в то время как вы хотите разделить их на две отдельные ячейки.

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

Предположим, что ваши исходные даты и время находятся в столбце A, следующая формула выполнит разделение:

=ЦЕЛОЕ(A2)
Используйте функцию INT для извлечения значений даты

Чтобы извлечь часть времени, вычтите дату, возвращаемую приведенной выше формулой, из исходного значения даты и времени:

=А2-В2

Где столбец A содержит исходные значения даты и времени, а столбец B содержит даты, возвращаемые функцией INT.

Если вы не хотите, чтобы значения времени были связаны с отдельными датами (например, вы можете удалить столбец даты в будущем), вы можете использовать следующую формулу MOD, которая относится только к исходным данным:

= ОСТАТ (A2,1)
Разделение даты и времени в Excel

Кончик. Если разделенные значения даты и времени отображаются неправильно, измените формат новых столбцов на «Дата» и «Время» соответственно.

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

Как записать время в Excel

Иногда вам может понадобиться преобразовать время в формат, который выглядит примерно так: «# дней, # часов, # минут и # секунд».. Хорошо, что вы уже знаете все ингредиенты формулы:

  • Извлечь дни с помощью функции INT;
  • Извлечение единиц времени с помощью функций ЧАС, МИНУТА и СЕКУНД, а также
  • Объединить все части в одну формулу.

Возникли трудности с определением правильной формулы для вашего рабочего листа? Следующий пример облегчит задачу!

Предположим, у вас есть даты предстоящих событий в столбце B, начиная с ячейки B4, и текущая дата и время, возвращаемые функцией NOW() в ячейке B1.

Формула для расчета разницы во времени так же проста, как =B4-$B$1. Конечно, ничто не мешает вам вычесть текущую дату и время напрямую с помощью =B4-NOW().

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

Формула для ввода в ячейку D4 выглядит следующим образом:

=ЦЕЛОЕ(C4) & «дни», & ЧАС(C4) & «часы», & МИНУТЫ(C4) & «минуты и» & СЕКУНДЫ(C4) & «секунды»
Формула для написания времени в Excel

Если вы хотите избавиться от значений 0, как в ячейках D6 и D7 на снимке экрана выше, включите следующие операторы IF:

=ЕСЛИ(ЦЕЛОЕ(C4)>0, ЦЕЛОЕ(C4)&» дней, «,»») & ЕСЛИ(ЧАС(C4)>0, ЧАС(C4) & » часов, «,»») & ЕСЛИ(МИНУТЫ (C4)>0, МИНУТЫ(C4) & «минуты и «,»») & ЕСЛИ(СЕКУНДЫ(C4)>0, СЕКУНДЫ(C4) & «секунды»,»»)

Все нули исчезли!
Формула для написания времени в Excel, за исключением нулевых значений

Примечание. Когда любая из приведенных выше формул ссылается на отрицательное число, функция #ЧИСЛО! появится ошибка. Это может произойти, когда вы вычитаете большее время из меньшего.

Альтернативный способ записать время словами в Excel — применить к ячейке следующий пользовательский формат времени: д «день», ч «часы», м «минуты» и «с» секунды. Никаких формул и расчетов не требуется! Дополнительные сведения см. в разделе Создание пользовательского формата времени в Excel.

Преобразование текста во время в Excel

Если ваши формулы времени и расчеты не работают правильно, причиной часто являются значения времени, отформатированные как текст. Самый быстрый способ преобразовать текст во время в Excel — использовать функцию ВРЕМЗНАЧ.

Функция Excel ВРЕМЗНАЧ имеет только один аргумент:

ВРЕМЗНАЧ(время_текст)

Time_text — это текстовая строка в любом из форматов времени, которые может распознать Excel. Например:

=ВРЕМЗНАЧ(«18:20»)

=ВРЕМЗНАЧ(«6 января 2015 г., 18:20»)

=ВРЕМЗНАЧ(A2), где A2 содержит текстовую строку
Преобразование текста во время с помощью функции Excel ЗНАЧ ВРЕМЕНИ

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

Преобразование времени в текст в Excel

Предположим, у вас есть файл Excel, полный времени, отформатированного так, чтобы он выглядел как «8:30:00», и вы хотите преобразовать его в текстовый формат. Простое изменение формата ячейки на ТЕКСТ не сработает, потому что это изменит ваши значения времени на базовое числовое представление времени. Например, 8:30:00 будет преобразовано в десятичное число 0,354166666666667.

Итак, как вы конвертируете ячейки в текстовый формат, чтобы в ваших ячейках все еще было время? Ответ заключается в использовании функции ТЕКСТ, которая преобразует числовое значение в текст с заданным форматированием отображения, например:

=ТЕКСТ($A2;»ч:мм:сс»)

На скриншоте ниже показаны другие возможные форматы:
Преобразование времени в текст в Excel

Как преобразовать числа в формат времени в Excel

Если у вас есть список чисел, таких как 1, 2, 3,5, и вы хотите преобразовать их в формат времени, например 1:00:00, 2:00:00 или 3:30, выполните следующие шаги.

  1. Разделите числа на 24 (в сутках 24 часа). Формула может быть такой простой, как =A2/24.
  2. Выберите ячейку (ячейки) с результатом формулы, щелкните правой кнопкой мыши и выберите Формат ячеек из контекстного меню или нажмите Ctrl+1. В любом случае, Формат ячеек появится диалоговое окно, вы выбираете Время на левой панели под Категорияи выберите нужный формат на правой панели в разделе Тип. Дополнительные сведения см. в разделе Как применить или изменить формат времени в Excel.

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

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

Учебник по времени Excel:

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *