преобразовать время в десятичное число, часы, минуты или секунды
В учебнике демонстрируются различные способы преобразования времени в десятичное число в Excel. Вы найдете множество формул для преобразования времени в часы, минуты или секунды, а также для преобразования текста во время и наоборот.
Поскольку Microsoft Excel использует числовую систему для хранения времени, вы можете легко преобразовать часы, минуты и секунды в числа, которые можно использовать в других вычислениях.
В общем, есть два способа преобразовать время в десятичное число в Excel — путем изменения формата ячейки и с помощью арифметических вычислений или функций времени Excel, таких как ЧАС, МИНУТА и СЕКУНД. Далее в этом уроке вы найдете подробное объяснение первого способа и примеры формул, демонстрирующие другой метод.
Как преобразовать время в десятичное число в Excel
Всего существует три способа преобразования значения времени в десятичное число: арифметическая операция, функция ПРЕОБРАЗОВАТЬ или комбинация трех различных функций времени.
Самый простой способ преобразовать время в десятичное число в Excel — умножить исходное значение времени на количество часов, секунд или минут в сутках:
- Чтобы преобразовать время в количество часов, умножьте время на 24, то есть количество часов в сутках.
- Чтобы перевести время в минуты, умножьте время на 1440, то есть количество минут в сутках (24*60).
- Чтобы преобразовать время в секунды, умножьте время на 86400, что равно количеству секунд в сутках (24*60*60).
В следующих разделах вы узнаете о других методах преобразования времени в десятичное число в Excel.
Как перевести время в часы в Excel
В этом разделе демонстрируются 3 различные формулы для преобразования часов из стандартного формата времени (чч:мм:сс) в десятичное число.
Формула 1: арифметический расчет
Вы уже знаете самый быстрый способ преобразования значения времени в количество часов в Excel — умножение на 24, то есть на количество часов в одних сутках:
=A2*24 (где A2 — значение времени)
Чтобы получить количество часов соревнований, вставьте приведенную выше формулу в функцию INT, которая избавится от дробной части:
=ЦЕЛОЕ(A2*24)
Формула 2: функция ПРЕОБРАЗОВАТЬ
Другой способ выполнить преобразование «время > часы» — использовать следующую формулу преобразования:
=ПРЕОБРАЗОВАТЬ(A2, “день”, “час”)
Формула 3: функции ЧАС, МИНУТА и СЕКУНД
Наконец, можно использовать несколько более сложную формулу, логика которой, впрочем, вполне очевидна. Извлеките отдельные единицы времени с помощью функций HOUR, MINUTE и SECOND, затем разделите минуты на 60 (количество минут в часе) и секунды на 3600 (количество секунд в часе) и сложите результаты:
= ЧАС(A2) + МИНУТЫ(A2)/60 + СЕКУНДЫ(A2)/3600
Как перевести время в минуты в Excel
Те же три метода можно использовать для преобразования минут из стандартного формата времени в десятичное число.
Формула 1: арифметический расчет
Чтобы преобразовать время в общее количество минут, вы умножаете время на 1440, что является количеством минут в одном дне (24 часа * 60 минут = 1440):
=А2*1440
Если вы хотите вернуть количество минут соревнований, используйте функцию INT, как в предыдущем примере:
=ЦЕЛОЕ(A2*1440)
Вы можете просмотреть результаты на скриншоте ниже:
Формула 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
Формула 2: функция ПРЕОБРАЗОВАТЬ
Формула в основном такая же, как и в приведенных выше примерах, с той лишь разницей, что вы конвертируете «день» в «сек»:
=ПРЕОБРАЗОВАТЬ(A2, “день”, “сек”)
Формула 3: функции ЧАС, МИНУТА и СЕКУНД
Используйте функции HOUR, MINUTE и SECOND, как в двух предыдущих примерах (я полагаю, что на данный момент вам не нужны дальнейшие объяснения логики формулы 🙂
=ЧАС(A2)*3600 + МИНУТЫ(A2)*60 + СЕКУНДЫ(A2)
Советы:
- Если какая-либо из приведенных выше формул возвращает значение, отформатированное как время, просто измените формат ячейки на Общий, чтобы отобразить его в виде числа.
- Чтобы преобразовать время в десятичное число, представляющее время во внутренней системе Excel, примените Общий форматировать в ячейку. При таком подходе 23:59:59 будет преобразовано в 0,99999, 06:00 — в 0,25, а 12:00 — в 0,5. Если целая часть преобразованного числа больше нуля, это означает, что ваша ячейка содержит значения даты и времени.
Как разделить дату и время в Excel
Как это часто бывает, ваш рабочий лист Excel может содержать даты и время в одной ячейке, в то время как вы хотите разделить их на две отдельные ячейки.
Помня, что во внутренней системе Excel значение даты хранится как целая часть, а значение времени как дробная часть десятичного числа, вы можете извлечь дату, используя функцию INT, которая округляет значение ячейки до ближайшего целого числа.
Предположим, что ваши исходные даты и время находятся в столбце A, следующая формула выполнит разделение:
=ЦЕЛОЕ(A2)
Чтобы извлечь часть времени, вычтите дату, возвращаемую приведенной выше формулой, из исходного значения даты и времени:
=А2-В2
Где столбец A содержит исходные значения даты и времени, а столбец B содержит даты, возвращаемые функцией INT.
Если вы не хотите, чтобы значения времени были связаны с отдельными датами (например, вы можете удалить столбец даты в будущем), вы можете использовать следующую формулу MOD, которая относится только к исходным данным:
= ОСТАТ (A2,1)
Кончик. Если разделенные значения даты и времени отображаются неправильно, измените формат новых столбцов на «Дата» и «Время» соответственно.
Вот как вы разделяете дату и время в Excel. Если вы хотите дополнительно разделить часы, минуты и секунды на отдельные столбцы, используйте функции ЧАС, МИНУТА и СЕКУНД, как показано в разделе Как получить часы, минуты и секунды из метки времени.
Как записать время в Excel
Иногда вам может понадобиться преобразовать время в формат, который выглядит примерно так: «# дней, # часов, # минут и # секунд”.. Хорошо, что вы уже знаете все ингредиенты формулы:
- Извлечь дни с помощью функции INT;
- Извлечение единиц времени с помощью функций ЧАС, МИНУТА и СЕКУНД, а также
- Объединить все части в одну формулу.
Возникли трудности с определением правильной формулы для вашего рабочего листа? Следующий пример облегчит задачу!
Предположим, у вас есть даты предстоящих событий в столбце B, начиная с ячейки B4, и текущая дата и время, возвращаемые функцией NOW() в ячейке B1.
Формула для расчета разницы во времени так же проста, как =B4-$B$1. Конечно, ничто не мешает вам вычесть текущую дату и время напрямую с помощью =B4-NOW().
А теперь давайте сделаем таймер обратного отсчета, который бы показывал, сколько дней, часов, минут и секунд осталось до каждого события.
Формула для ввода в ячейку D4 выглядит следующим образом:
=ЦЕЛОЕ(C4) & “дни”, & ЧАС(C4) & “часы”, & МИНУТЫ(C4) & “минуты и” & СЕКУНДЫ(C4) & “секунды”
Если вы хотите избавиться от значений 0, как в ячейках D6 и D7 на снимке экрана выше, включите следующие операторы IF:
=ЕСЛИ(ЦЕЛОЕ(C4)>0, ЦЕЛОЕ(C4)&” дней, “,””) & ЕСЛИ(ЧАС(C4)>0, ЧАС(C4) & ” часов, “,””) & ЕСЛИ(МИНУТЫ (C4)>0, МИНУТЫ(C4) & “минуты и “,””) & ЕСЛИ(СЕКУНДЫ(C4)>0, СЕКУНДЫ(C4) & “секунды”,””)
Все нули исчезли!
Примечание. Когда любая из приведенных выше формул ссылается на отрицательное число, функция #ЧИСЛО! появится ошибка. Это может произойти, когда вы вычитаете большее время из меньшего.
Альтернативный способ записать время словами в Excel — применить к ячейке следующий пользовательский формат времени: д “день”, ч “часы”, м “минуты” и “с” секунды. Никаких формул и расчетов не требуется! Дополнительные сведения см. в разделе Создание пользовательского формата времени в Excel.
Преобразование текста во время в Excel
Если ваши формулы времени и расчеты не работают правильно, причиной часто являются значения времени, отформатированные как текст. Самый быстрый способ преобразовать текст во время в Excel — использовать функцию ВРЕМЗНАЧ.
Функция Excel ВРЕМЗНАЧ имеет только один аргумент:
ВРЕМЗНАЧ(время_текст)
Time_text — это текстовая строка в любом из форматов времени, которые может распознать Excel. Например:
=ВРЕМЗНАЧ(“18:20”)
=ВРЕМЗНАЧ(“6 января 2015 г., 18:20”)
=ВРЕМЗНАЧ(A2), где A2 содержит текстовую строку
Как видите, формулы со ссылками на ячейки и соответствующими текстовыми строками дают идентичные результаты. Кроме того, обратите внимание на выравнивание по левому краю строк времени (текстовых значений) в ячейках A2 и A6 и выравнивание по правому краю преобразованных значений времени в столбце D.
Преобразование времени в текст в Excel
Предположим, у вас есть файл Excel, полный времени, отформатированного так, чтобы он выглядел как «8:30:00», и вы хотите преобразовать его в текстовый формат. Простое изменение формата ячейки на ТЕКСТ не сработает, потому что это изменит ваши значения времени на базовое числовое представление времени. Например, 8:30:00 будет преобразовано в десятичное число 0,354166666666667.
Итак, как вы конвертируете ячейки в текстовый формат, чтобы в ваших ячейках все еще было время? Ответ заключается в использовании функции ТЕКСТ, которая преобразует числовое значение в текст с заданным форматированием отображения, например:
=ТЕКСТ($A2;”ч:мм:сс”)
На скриншоте ниже показаны другие возможные форматы:
Как преобразовать числа в формат времени в Excel
Если у вас есть список чисел, таких как 1, 2, 3,5, и вы хотите преобразовать их в формат времени, например 1:00:00, 2:00:00 или 3:30, выполните следующие шаги.
- Разделите числа на 24 (в сутках 24 часа). Формула может быть такой простой, как =A2/24.
- Выберите ячейку (ячейки) с результатом формулы, щелкните правой кнопкой мыши и выберите Формат ячеек из контекстного меню или нажмите Ctrl+1. В любом случае, Формат ячеек появится диалоговое окно, вы выбираете Время на левой панели под Категорияи выберите нужный формат на правой панели в разделе Тип. Дополнительные сведения см. в разделе Как применить или изменить формат времени в Excel.
Это все на сегодня. Если кто-то хочет получить непосредственный опыт работы с формулами, обсуждаемыми в этой статье, добро пожаловать на загрузку Образец рабочей тетради для преобразования времени в Excel.
Если вы хотите узнать еще несколько полезных формул для сложения и вычитания времени, расчета прошедшего времени и разницы во времени, ознакомьтесь с другими частями нашего учебника по времени в Excel (ссылки доступны в конце этой страницы). Я благодарю вас за чтение и надеюсь увидеть вас снова на следующей неделе.
