По умолчанию дата и время сохраняются в Excel в виде чисел / десятичных знаков. Но вы часто видите это в разных форматах из-за способа форматирования ячеек.
Например, в следующем примере значения в столбцах A и B одинаковы, но они отображаются по-разному из-за формата.
И одна из вещей, которые многим нужны в Excel, — это преобразование времени в десятичное (например, часы, минуты или секунды).
Например, вместо даты и времени 01-01-2020 6:30 AM вы можете получить:
- Количество часов вроде 6
- Минут вроде 30
К счастью, в Excel есть отличные формулы, с помощью которых вы можете легко преобразовать время в десятичные числа.
Как преобразовать время в десятичные числа в Excel
В этом уроке я покажу вам несколько примеров преобразования времени в десятичное с помощью формул (например, преобразование времени в часы, минуты и секунды).
Есть несколько способов преобразовать время в десятичное в Excel :
- Использование арифметических операций — самый простой способ преобразовать время в десятичные числа. Например:
- Чтобы преобразовать время в часы, умножьте значение часа на 24.
- Чтобы преобразовать время в минуты, умножьте значение времени на 24 * 60.
- Чтобы преобразовать время в секунды, умножьте значение времени на 24 * 60 * 60.
- Вы также можете использовать встроенные формулы Excel, такие как ПРЕОБРАЗОВАТЬ или ЧАС / МИНУТУ / СЕКУНДУ.
В следующих разделах я покажу вам примеры того, как преобразовать время Excel в десятичные значения с помощью этих методов.
Итак, приступим!
Преобразование времени в часы в Excel
В этом разделе я расскажу о трех различных способах преобразования времени в часы в Excel.
Использование простого умножения для получения значения часа
Допустим, у вас есть время в ячейке, например 18:00 (в ячейке A2), и вы хотите преобразовать его в часы.
Вы можете просто умножить это на 24, и это даст вам, сколько часов прошло до этого времени.
= LA2 * 24
Это работает, потому что Excel хранит дату и время в виде чисел. Полный день (24 часа) сохраняется как 1 в Excel. Это означает, что каждый час сохраняется как 1/24.
Таким образом, хотя вы видите значение 18:00 в бэкэнде, оно сохраняется как значение 0,75 (что указывает на то, что уже прошло 18 часов из 24). Итак, если вы умножите это на 24, вы получите количество прошедших часов.
Если у вас есть значение вроде 18:32, использование приведенной выше формулы даст вам результат типа 18,53 (где доли минут / секунд отображаются в виде десятичных чисел, а полные / полные часы отображаются в виде целых чисел). В этом примере числовое значение 32 минуты в Excel будет 0,53 часа.
Примечание. Если вы получаете время с десятичными знаками (например, 18,53), обязательно отформатируйте его так, чтобы десятичные разряды также отображались. Если нет, вы можете увидеть результат как 19, поскольку Excel показывает округленное целое число. Однако это не изменит значение в ячейке, которое по-прежнему будет 18,53.
Если вам просто нужно значение полного / полного часа и игнорировать долю минут, используйте следующую формулу, используя INT.
= ЦЕЛОЕ (LA2 * 24)
INT просто дает вам всю часть, и вам не нужно беспокоиться о лишних минутах.
Использование функций Excel (HOUR, MINUTE, и SECOND)
Если вам не нравится ручной метод умножения, еще один действительно простой способ преобразовать время в часы — использовать встроенные функции времени (ЧАС, МИНУТА и СЕКУНДА).
Допустим, у нас есть время, показанное ниже, и мы хотим знать, сколько часов прошло за день.
Ниже приведена формула перевода времени в часы. = ЧАС (A2) + МИНУТА (A2) / 60 + СЕКУНД (A2) / (60 * 60)
Функция ЧАС просто возвращает количество часов, прошедших за заданное время, точно так же, как функции МИНУТА и ВТОРАЯ.
Но поскольку нам нужно окончательное значение в часах, нам нужно разделить значение минут на 60 (чтобы преобразовать его в часы), а второе значение на 60 * 60 (снова, чтобы преобразовать его в часы).
Использование функции преобразования (CONVERT)
Еще один очень простой (возможно, самый простой из трех методов) — использовать функцию CONVERT.
Эта функция принимает числовое значение (в данном случае время) и может преобразовывать его в часы (или минуты / секунды).
Ниже приведена формула, преобразующая время в часы:
= ПРЕОБРАЗОВАТЬ (A2; «день»; «время»)
Второй аргумент — это функция существующего формата данных, а третий аргумент — это формат, в который вы хотите преобразовать.
Примечание. Иногда результаты могут отличаться от ожидаемых. Это может быть связано с форматом ячейки, который может отображать результат в формате даты, а не в десятичном формате. Вы можете легко изменить это, перейдя на вкладку «Главная» и установив формат в раскрывающемся списке (он находится в группе «Число»).
На практике у вас больше шансов получить вместе дату и время (а не только время, как я показал в примерах выше). Вы по-прежнему можете использовать методы, показанные выше, но вам нужно сначала разделить значение даты / времени, а затем использовать методы, показанные выше, только для части времени. Позже в этом руководстве я также расскажу о двух методах разделения даты и времени в Excel с помощью формул.
Вы также можете использовать описанные выше методы для преобразования времени в минуты и секунды.
Давайте также быстро взглянем на эти примеры.
Преобразование времени в минуты в Excel
В этом разделе я расскажу о трех различных способах преобразования времени в минуты в Excel.
Использование простого умножения для получения минутного значения
В сутках 1440 минут (24 * 60).
Итак, чтобы преобразовать время в минуты, вы можете просто умножить значение времени на 1440.
Допустим, у вас есть время в ячейке, например 18:00 (в ячейке A2), и вы хотите преобразовать его в минуты.
Ниже приведена формула для этого:
= A2 * 1440
Это даст вам общее количество минут, прошедших в тот день в указанное время.
Примечание. Помните, что если у вас есть секунды, они будут преобразованы в минуты и показаны как десятичные. Чтобы они были видны в результате, полученные ячейки должны быть отформатированы так, чтобы отображать результаты с 2/3 десятичными знаками.
Если вам нужно только значение минут и игнорировать часть секунд, используйте следующую формулу, используя INT.
= ЦЕЛОЕ (A2 * 1440)
Использование функций Excel (HOUR, MINUTE, и SECOND)
Так же, как мы использовали эти функции для получения времени, вы также можете использовать их для преобразования времени в минуты.
Ниже приведена формула, которая преобразует время в минуты с помощью этих функций:
= ЧАС (A2) * 60 + МИНУТЫ (A2) + СЕКУНДЫ (A2) / 60
Поскольку цель здесь — получить все части в минутах (часы, минуты и секунды), мы умножаем 60 на часы, чтобы получить минуты, и делим секунды на 60, чтобы получить минуты.
Использование функции преобразования
Опять же, самый простой способ конвертировать время в минуты — использовать формулу преобразования.
Следующая формула преобразует значение часа в минуты;
= ПРЕОБРАЗОВАТЬ (A2; «день»; «мин»)
Второй аргумент сообщает функции, что значение в ячейке A2 находится в формате дня, а третий аргумент («mn») указывает функции преобразовать его в минуты.
Преобразование времени в секунды в Excel
В этом разделе я расскажу о трех различных способах преобразования времени в секунды в Excel.
Использование простого умножения для получения значения секунд
В сутках 86400 секунд (24 * 60 * 60).
Итак, чтобы преобразовать время в секунды, вы можете просто умножить значение времени на 86400.
Допустим, у вас есть время в ячейке, например 18:32:30 (в ячейке A2), и вы хотите преобразовать его в секунды.
Ниже приведена формула для этого:
= A2 * 86400
Это даст вам общее количество секунд, которые прошли в тот день в указанное время.
Использование функций Excel (HOUR, MINUTE и SECOND)
Ниже приведена формула, которая преобразует время в минуты с помощью этих функций:
= ЧАС (A2) * 60 * 60 + МИНУТЫ (A2) * 60 + СЕКУНДЫ (A2)
Поскольку цель здесь — получить все части (часы, минуты и секунды) в секундах, мы умножаем 60 * 60 на часы, чтобы получить секунды, и умножаем минуты на 60, чтобы получить секунды.
Разделить дату и время, а затем преобразовать время в десятичное
До сих пор мы видели примеры, когда у нас есть только время, и мы хотели бы преобразовать это время в часы, минуты или секунды.
Но что, если у вас есть дата и время?
В этом случае указанные выше методы нельзя использовать напрямую. Сначала необходимо разделить дату и время, а затем преобразовать время в десятичные числа. Поскольку дата и время хранятся в Excel в виде чисел, их разделение становится проще.
Весь день сохраняется как целое число, а часть времени сохраняется как десятичное значение. Поэтому, если вы хотите разделить дату и время, просто разделите целую часть и десятичную часть.
Давайте рассмотрим несколько действительно простых способов разделить дату и время в Excel.
Разделить дату и время с помощью формулы INT
Допустим, у нас есть набор данных, показанный ниже, и мы хотим разделить дату и время.
Для этого введите в ячейку B2 следующую формулу: = A2-INT (A2)
Приведенная выше формула удаляет целую часть и дает вам десятичную часть даты.
Теперь вы можете использовать один из трех методов выше (используя арифметическое умножение, используя формулу ЧАС / МИНУТА / СЕКУНДА или формулу ПРЕОБРАЗОВАТЬ) для преобразования часа в десятичное.
Разделите дату и время с помощью формулы MOD
Другой способ сделать это — использовать функцию MOD.
Допустим, у нас есть набор данных, показанный ниже, и мы хотим разделить дату и время.
Для этого введите в ячейку B2 следующую формулу:
= МОД (A2; 1)
Это сразу даст вам десятичную часть, поэтому вы можете использовать один из трех методов, описанных выше, для преобразования времени в десятичные числа (часы, минуты или секунды).