По умолчанию даты и время хранятся в Excel как числа/десятичные числа. Но вы часто будете видеть это в разных форматах из-за того, как отформатированы ячейки.
Например, в приведенном ниже примере значения в столбцах A и B одинаковы, но отображаются по-разному из-за форматирования.
И одна из вещей, которая нужна многим людям в Excel, — это преобразование времени в десятичные дроби (например, часы, минуты или секунды).
Например, вместо даты и времени 01-01-2020 6:30 вы можете получить:
- Количество часов как 6
- Количество минут как 30
К счастью, в Excel есть несколько отличных формул, которые могут легко преобразовывать время в десятичные дроби.
Как преобразовать время в десятичные числа в Excel
В этом уроке я покажу вам несколько примеров преобразования времени в десятичное число с использованием формул (т е преобразования времени в часы, минуты и секунды).
Есть несколько способов преобразовать время в десятичное число в Excel :
- Использование арифметических операций — самый простой способ преобразовать время в десятичные дроби. Например:
- Чтобы перевести время в часы, умножьте значение времени на 24.
- Чтобы перевести время в минуты, умножьте значение времени на 24 * 60.
- Чтобы перевести время в секунды, умножьте значение времени на 24 * 60 * 60.
- Вы также можете использовать встроенные формулы Excel, такие как ПРЕОБРАЗОВАТЬ или ЧАС/МИНУТА/СЕКУНД.
В следующих разделах я покажу вам примеры того, как преобразовать время Excel в десятичные значения с помощью этих методов.
Тогда давайте начнем!
Преобразование времени в часы в Excel
В этом разделе я расскажу о трех различных способах преобразования времени в часы в Excel.
Использование простого умножения для получения значения часа
Допустим, у вас есть время в ячейке, например 18:00 (в ячейке A2), и вы хотите преобразовать его в часы.
Вы можете просто умножить это на 24, и это даст вам, сколько часов уже прошло.
= А2 * 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.
= МЕЖ(A2*24)
INT дает вам только целую часть, и вам не нужно беспокоиться о дополнительных минутах.
Использование функций Excel (HOUR, MINUTE, и SECOND)
Если вам не нравится метод ручного умножения, другой очень простой способ конвертировать время в часы — это использовать встроенные функции, связанные со временем (ЧАС, МИНУТА и СЕКУНДА).
Предположим, у вас есть время, показанное ниже, и вы хотите узнать, сколько часов прошло за день.
Ниже приведена формула перевода времени в часы. =ЧАС(A2)+МИНУТЫ(A2)/60+СЕКУНДЫ(A2)/(60*60)
Функция HOUR просто возвращает количество часов, прошедших в заданное время, как и функции MINUTE и SECOND.
Но так как нам нужно конечное значение в часах, нам нужно разделить значение минут на 60 (чтобы преобразовать в часы) и второе значение на 60 * 60 (опять же, чтобы преобразовать в часы).
Использование функции преобразования (CONVERT)
Еще один очень простой способ (возможно, самый простой из трех) — использовать функцию ПРЕОБРАЗОВАТЬ.
Эта функция принимает числовое значение (в данном случае время) и может преобразовывать его в часы (или минуты/секунды).
Ниже приведена формула перевода времени в часы:
=ПРЕОБРАЗОВАТЬ(A2,»день»,»час»)
Второй аргумент — это функция существующего формата данных, а третий аргумент — это формат, в который вы хотите преобразовать.
Примечание. Иногда результаты могут отличаться от ожидаемых. Это может быть связано с форматированием ячейки, которое может отображать результат в формате даты, а не в десятичном формате. Вы можете легко изменить это, перейдя на вкладку «Главная» и установив формат в раскрывающемся меню (оно находится в группе «Число»).
На практике у вас больше шансов получить дату и время вместе (а не только время, как я показал в примерах выше). Вы по-прежнему можете использовать методы, показанные выше, но вам нужно сначала разделить значение даты и времени, а затем использовать методы, показанные выше, только для временной части. Позже в этом уроке я также расскажу о двух методах разделения даты и времени в Excel с помощью формул.
Вы также можете использовать те же методы, что и выше, для преобразования времени в минуты и секунды.
Давайте также кратко рассмотрим эти примеры.
Преобразование времени в минуты в Excel
В этом разделе я расскажу о трех различных способах преобразования времени в минуты в Excel.
Использование простого умножения для получения минутного значения
В сутках 1440 минут (24*60).
Таким образом, чтобы перевести время в минуты, вы можете просто умножить значение времени на 1440.
Предположим, у вас есть время в ячейке, например 18:00 (в ячейке A2), и вы хотите преобразовать его в минуты.
Ниже приведена формула для этого:
=А2*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), и вы хотите преобразовать его в секунды.
Ниже приведена формула для этого:
=А2*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)
Это сразу даст вам десятичную часть, а затем вы можете использовать любой из трех методов, описанных выше, для преобразования времени в десятичные числа (часы, минуты или секунды). Преобразование времени в десятичное число в Excel (часы, минуты, секунды)
Рекомендуемые! Пройдите очень эффективный онлайн-курс по Excel и таблицам Google для начинающих.