Как преобразовать часы и минуты в десятичные числа в Excel
Обновлено: 03.07.2024
В этом руководстве демонстрируются различные способы преобразования времени в десятичное число в Excel. Вы найдете множество формул для преобразования времени в часы, минуты или секунды, а также для преобразования текста во время и наоборот.
Поскольку Microsoft Excel использует числовую систему для хранения времени, вы можете легко преобразовывать часы, минуты и секунды в числа, которые можно использовать в других вычислениях.
В целом существует два способа преобразования времени в десятичное число в Excel: изменение формата ячейки и использование арифметических вычислений или функций времени Excel, таких как ЧАС, МИНУТА и СЕКУНДА. Далее в этом руководстве вы найдете подробное объяснение первого способа и примеры формул, демонстрирующие другой метод.
Как преобразовать время в десятичное число в Excel
В целом существует три способа преобразования значения времени в десятичное число: арифметическая операция, функция ПРЕОБРАЗОВАТЬ или комбинация трех различных функций времени.
Самый простой способ преобразовать время в десятичное число в Excel — умножить исходное значение времени на количество часов, секунд или минут в сутках:
- Чтобы преобразовать время в количество часов, умножьте время на 24 — количество часов в сутках.
- Чтобы преобразовать время в минуты, умножьте время на 1440 — количество минут в сутках (24 * 60).
- Чтобы преобразовать время в секунды, умножьте время на 86 400 — количество секунд в сутках (24 * 60 * 60 ).
В следующих разделах вы узнаете о других методах преобразования времени в десятичное число в Excel.
Как преобразовать время в часы в Excel
В этом разделе представлены 3 различные формулы для преобразования часов из стандартного формата времени (чч:мм:сс) в десятичное число.
Формула 1: арифметический расчет
Вы уже знаете самый быстрый способ преобразования значения времени в количество часов в Excel — умножение на 24, т. е. на количество часов в сутках:
=A2*24 (где A2 — значение времени)
Чтобы получить количество часов соревнований, вставьте приведенную выше формулу в функцию INT, которая избавится от дробной части:
Формула 2: функция ПРЕОБРАЗОВАТЬ
Другой способ выполнить преобразование "время > часы" – использовать следующую формулу преобразования:
=ПРЕОБРАЗОВАТЬ(A2, "день", "час")
Формула 3: функции ЧАС, МИНУТА и СЕКУНДА
Наконец, можно использовать немного более сложную формулу, логика которой, впрочем, вполне очевидна. Извлеките отдельные единицы времени с помощью функций ЧАС, МИНУТА и СЕКУНД, затем разделите минуты на 60 (количество минут в часе) и секунды на 3600 (количество секунд в часе) и сложите результаты: /p>
=ЧАС(A2) + МИНУТЫ(A2)/60 + СЕКУНДЫ(A2)/3600
Как преобразовать время в минуты в Excel
Те же три метода можно использовать для преобразования минут из стандартного формата времени в десятичное число.
Формула 1: арифметический расчет
Чтобы преобразовать время в общее количество минут, умножьте время на 1440 — количество минут в одном дне (24 часа * 60 минут = 1440):
Если вы хотите вернуть количество минут соревнований, используйте функцию INT, как в предыдущем примере:
Вы можете просмотреть результаты на снимке экрана ниже:
Формула 2: функция ПРЕОБРАЗОВАТЬ
Чтобы выполнить преобразование "время > минуты" с помощью функции CONVERT(number, from_unit, to_unit), укажите "день" в качестве единицы для преобразования и "mn" в качестве единицы для преобразования:
=ПРЕОБРАЗОВАТЬ(A2, "день", "мин")
Формула 3: функции ЧАС, МИНУТА и СЕКУНДА
Еще один способ получить количество минут — умножить часы на 60 и разделить секунды на то же число:
=ЧАС(A2)*60 + МИНУТЫ(A2) + СЕКУНДЫ(A2)/60
Как преобразовать время в секунды в Excel
Преобразование времени в общее количество секунд в Excel можно выполнить аналогичным образом.
Формула 1: арифметический расчет
Умножьте значение времени на 86 400 – количество секунд в сутках (24 часа * 60 минут * 60 секунд = 86 400):
=A2*86400
Формула 2: функция ПРЕОБРАЗОВАТЬ
Формула в основном такая же, как в приведенных выше примерах, с той лишь разницей, что вы конвертируете единицу измерения «день» в «секунду»:
=ПРЕОБРАЗОВАТЬ(A2, "день", "сек")
Формула 3: функции ЧАС, МИНУТА и СЕКУНДА
Используйте функции ЧАС, МИНУТА и СЕКУНД, как в двух предыдущих примерах (думаю, на данном этапе вам не нужны дальнейшие объяснения логики формулы :)
=ЧАС(A2)*3600 + МИНУТЫ(A2)*60 + СЕКУНДЫ(A2)
- Если какая-либо из приведенных выше формул возвращает значение, отформатированное как время, просто измените формат ячейки на Общий, чтобы оно отображалось в виде числа.
- Чтобы преобразовать время в десятичное число, представляющее время во внутренней системе Excel, примените к ячейке формат Общий. При таком подходе 23:59:59 будет преобразовано в 0,99999, 06:00 — в 0,25, а 12:00 — в 0,5. Если целая часть преобразованного числа больше нуля, это означает, что ваша ячейка содержит значения даты и времени.
Как разделить дату и время в Excel
Как это часто бывает, лист Excel может содержать даты и время в одной ячейке, а вы хотите разделить их на две отдельные ячейки.
Помня, что во внутренней системе Excel значение даты хранится как целая часть, а значение времени как дробная часть десятичного числа, вы можете извлечь дату с помощью функции ЦЕЛОЕ, которая округляет значение ячейки в меньшую сторону. ближайшее целое число.
Предположим, что ваши исходные даты и время находятся в столбце A, следующая формула выполнит разделение:
=INT(A2)
Чтобы извлечь часть времени, вычтите дату, возвращенную приведенной выше формулой, из исходного значения даты и времени:
Где столбец A содержит исходные значения даты и времени, а столбец B содержит даты, возвращенные функцией INT.
Если вы не хотите, чтобы значения времени были связаны с отдельными датами (например, вы можете удалить столбец даты в будущем), вы можете использовать следующую формулу MOD, которая относится только к исходным данным:< /p>
=МОД(A2,1)
Совет. Если разделенные значения даты и времени отображаются неправильно, измените формат новых столбцов на Дата и время соответственно.
Так вы разделяете дату и время в Excel. Если вы хотите дополнительно разделить часы, минуты и секунды на отдельные столбцы, используйте функции ЧАС, МИНУТА и СЕКУНД, как показано в разделе Как получить часы, минуты и секунды из метки времени.
Как написать время в Excel
- Извлечь дни с помощью функции INT;
- Извлечение единиц времени с помощью функций ЧАС, МИНУТА и СЕКУНД, а также
- Объединить все части в одну формулу.
Возникли трудности с определением правильной формулы для рабочего листа? Следующий пример упростит задачу!
Предположим, что у вас есть даты предстоящих событий в столбце B, начиная с ячейки B4, а текущая дата и время, возвращаемые функцией NOW(), находятся в ячейке B1.
Формула для расчета разницы во времени проста: =B4-$B$1 . Конечно, ничто не мешает вам вычесть текущую дату и время напрямую с помощью =B4-NOW() .
А теперь давайте сделаем таймер обратного отсчета, который бы показывал, сколько дней, часов, минут и секунд осталось до каждого события.
Формула для ввода в ячейку D4 выглядит следующим образом:
=INT(C4) & " дни, " & HOUR(C4) & " часы, " & MINUTE(C4) & " минуты и " & SECOND(C4) & " секунды"
Если вы хотите избавиться от значений 0, как в ячейках D6 и D7 на снимке экрана выше, включите следующие операторы IF:
=IF(INT(C4)>0, INT(C4)&" дни, ","") & IF(HOUR(C4)>0, HOUR(C4) & " часы, ","") & ЕСЛИ(МИНУТЫ(C4)>0, МИНУТЫ(C4) & " минуты и ","") & ЕСЛИ(СЕКУНДЫ(C4)>0, СЕКУНДЫ(C4) & " секунды","")
Все нули удалены!
Альтернативный способ записи времени словами в Excel — применить к ячейке следующий настраиваемый формат времени: d "день", h "часы", m "минуты" и "s" секунды". Никаких формул и расчетов не требуется! Дополнительные сведения см. в разделе Создание пользовательского формата времени в Excel.
Преобразование текста во время в Excel
Если ваши формулы и расчеты времени работают неправильно, причиной часто являются значения времени, отформатированные как текст. Самый быстрый способ преобразовать текст во время в Excel — использовать функцию ЗНАЧ ВРЕМЕНИ.
Функция Excel ВРЕМЗНАЧ имеет только один аргумент:
Текст_времени – это текстовая строка в любом из форматов времени, распознаваемых Excel. Например:
=TIMEVALUE("6 января 2015 г., 18:20")
=TIMEVALUE(A2), где A2 содержит текстовую строку
Как видите, формулы со ссылками на ячейки и соответствующими текстовыми строками дают одинаковые результаты. Кроме того, обратите внимание на выравнивание по левому краю строк времени (текстовых значений) в ячейках A2 и A6 и выравнивание по правому краю преобразованных значений времени в столбце D.
Преобразование времени в текст в Excel
Предположим, у вас есть файл Excel, заполненный временем, отформатированным так, чтобы оно выглядело как "8:30:00", и вы хотите преобразовать его в текстовый формат. Простое изменение формата ячейки на ТЕКСТ не сработает, потому что это изменит ваши значения времени на базовое числовое представление времени. Например, 8:30:00 будет преобразовано в десятичное число 0,354166666666667.
Итак, как преобразовать ячейки в текстовый формат, чтобы в них по-прежнему отображалось время? Ответ заключается в использовании функции ТЕКСТ, которая преобразует числовое значение в текст с заданным форматированием отображения, например:
На снимке экрана ниже показаны другие возможные форматы:
Как преобразовать числа в формат времени в Excel
Если у вас есть список чисел, таких как 1, 2, 3,5, и вы хотите преобразовать их в формат времени, например 1:00:00, 2:00:00 или 3:30, выполните следующие действия. шагов.
- Поделите числа на 24 (в сутках 24 часа). Формула может быть такой простой, как =A2/24.
- Выберите ячейку (ячейки) с результатом формулы, щелкните правой кнопкой мыши и выберите Формат ячеек в контекстном меню или нажмите Ctrl+1 . В любом случае появится диалоговое окно Формат ячеек. Выберите Время на левой панели в разделе Категория и выберите нужный формат справа. в разделе Тип. Дополнительные сведения см. в разделе Как применить или изменить формат времени в Excel.
На сегодня все. Если кто-то хочет получить непосредственный опыт работы с формулами, обсуждаемыми в этой статье, вы можете загрузить образец рабочей книги для преобразования времени в Excel.
Если вы хотите изучить еще несколько полезных формул для сложения и вычитания времени, расчета прошедшего времени и разницы во времени, ознакомьтесь с другими частями нашего руководства по времени в Excel (ссылки доступны в конце этой страницы). Я благодарю вас за чтение и надеюсь увидеть вас снова на следующей неделе.
Если вы хотите преобразовать время в десятичное число (т. е. в количество часов, минут или секунд) в Excel, самый простой способ сделать это — умножить время на количество часов, секунд или минут в день.
- Чтобы преобразовать время в количество часов, умножьте его на 24 (количество часов в сутках);
- Чтобы преобразовать время в количество минут, умножьте его на 24*60 (количество минут в сутках);
- Чтобы преобразовать время в количество секунд, умножьте его на 24*60*60 (количество секунд в сутках).
Примечание. Если результат вашей формулы не отображается в виде десятичного числа, вам необходимо изменить формат ячейки, чтобы оно отображало десятичное число. Самый простой способ сделать это — выбрать ячейку, содержащую формулу, а затем выбрать тип формата «Общие» на вкладке «Главная» ленты Excel.
Тип формата «Общий» на ленте Excel
Для получения дополнительной информации с примерами см. следующие разделы:
Преобразование времени в десятичный индекс:
Конвертировать время в часы в Excel
В следующих таблицах показаны две разные формулы, которые можно использовать для преобразования времени в количество часов в Excel.
Метод 1: простое умножение
Простейшая формула для преобразования времени в количество часов показана ниже. В этом случае время просто умножается на 24 (количество часов в сутках).
| A | B | |
|---|---|---|
| 1 | Время (чч:мм:сс ) | Часы (десятичные) |
| 2 | 02:30:00 | =A2 * 24 |
Формула в ячейке B2 приведенной выше электронной таблицы возвращает значение 2,5.
Т.е. 2 часа 30 минут и 0 секунд равны 2,5 часам.
(Причина, по которой этот метод работает, заключается в том, что время в Excel хранится внутри в виде десятичных значений, где значение 1,0 используется для представления 24 часов, а значение 1/24 используется для представления 1 часа).< /я>
Способ 2. Использование функций времени Excel
Вторая формула, которая может использоваться для получения того же результата, использует функции Excel Hour, Minute и Second. Хотя эта формула намного длиннее, чем простое умножение, показанное выше, некоторые люди предпочитают ее.
| A | B | |
|---|---|---|
| 1 | Время (чч:мм:сс ) | Часы (десятичные) |
| 2 | 02:30:00 | =HOUR(A2 ) + МИНУТА(A2) / 60 + СЕКУНДА(A2) / 3600 |
И снова формула в ячейке B2 приведенной выше электронной таблицы возвращает значение 2,5.
Конвертировать время в минуты в Excel
В ячейке B2 приведенных ниже электронных таблиц показаны две формулы, которые можно использовать для преобразования времени в количество минут в Excel.
Метод 1: простое умножение
Самый простой способ преобразовать время в количество минут — умножить время на 1440, что равно 24*60 (количество минут в одном дне):
| A | B | |
|---|---|---|
| 1 | Время (чч:мм:сс ) | Минуты (десятичные) |
| 2 | 02:30:30 | =A2 * 1440 |
Для времени 02:30:30 формула в ячейке B2 приведенной выше таблицы возвращает значение 150,5.
Т.е. 2 часа 30 минут и 30 секунд равны 150,5 минутам.
(Причина, по которой этот метод работает, заключается в том, что время в Excel хранится внутри в виде десятичных значений, причем значение 1,0 используется для представления 24 часов, а значение 1/1440 используется для представления 1 минуты).< /я>
Способ 2. Использование функций времени Excel
Тот же результат можно получить с помощью функций Excel Hour, Minute и Second, как показано в таблице ниже:
| A | B | |
|---|---|---|
| 1 | Время (чч:мм:сс ) | Минуты (десятичные) |
| 2 | 02:30:30 | =HOUR(A2 ) * 60 + МИНУТА(A2) + СЕКУНДА(A2) / 60 |
И снова формула в ячейке B2 приведенной выше электронной таблицы возвращает значение 150,5.
Конвертировать время в секунды в Excel
В таблицах ниже показаны формулы, которые можно использовать для преобразования времени в количество секунд в Excel.
Метод 1: простое умножение
Самый простой способ преобразовать время в количество секунд — просто умножить время на 86400, что равно 24*60*60 (количество секунд в одном дне):
| A | B | |
|---|---|---|
| 1 | Время (чч:мм:сс ) | Секунды (десятичные) |
| 2 | 02:30:30 | =A2 * 86400 |
Для времени 02:30:30 формула в ячейке B2 приведенной выше таблицы возвращает значение 9030.
Т.е. 2 часа 30 минут и 30 секунд равны 9030 секундам.
(Причина, по которой этот метод работает, заключается в том, что время в Excel хранится внутри в виде десятичных значений, причем значение 1,0 используется для представления 24 часов, а значение 1/86400 используется для представления 1 секунды).< /я>
Способ 2. Использование функций времени Excel
Тот же результат можно получить, используя функции Excel Hour, Minute и Second, как показано ниже:
| A | B | |
|---|---|---|
| 1 | Время (чч:мм:сс ) | Секунды (десятичные) |
| 2 | 02:30:30 | =HOUR(A2 ) * 3600 + МИНУТА(A2) * 60 + СЕКУНДА(A2) |
Опять же, формула в ячейке B2 приведенной выше таблицы возвращает значение 9030.
Форматирование результата
При преобразовании времени в десятичное число ячейка, содержащая результат, может иметь неправильный формат (например, результат может отображаться в виде времени, а не десятичного числа).
В этом случае вам потребуется отформатировать ячейку для отображения десятичного числа. Для этого вы можете использовать любой из следующих двух методов:
Способ 1 – форматирование с помощью параметров ленты
Самый простой способ изменить форматирование ячейки – выбрать ячейки, которые необходимо отформатировать, а затем выбрать числовой вариант (например,Общие) из раскрывающегося меню на ленте. Этот параметр находится в группе «Число» на вкладке «Главная» (см. ниже):
Способ 2. Форматирование с помощью диалогового окна «Формат ячеек»
Этот метод можно использовать во всех версиях Excel, включая более ранние версии, в которых нет ленты:
Если у вас есть время, сохраненное как 1:30, но вы хотите, чтобы оно было десятичным (1,50), или если вы хотите преобразовать время в минуты/секунды, это руководство для вас! Преобразование времени в десятичные дроби не составит труда, если вы знаете эти три способа.
Арифметический способ
Самый простой способ преобразовать время в десятичную форму в Excel — умножить исходное значение на количество часов, секунд или минут в сутках.
- Чтобы перевести в часы, умножьте на 24 (количество часов в сутках).
- Чтобы перевести в минуты, умножьте на 1440 (количество минут в сутках).
- Чтобы перевести в секунды, умножьте на 86 400 (количество секунд в сутках).
Сначала вам нужно щелкнуть правой кнопкой мыши столбец времени, выбрать «Формат ячеек» и убедиться, что для них установлены следующие значения:
< /p>
Для всех ваших десятичных столбцов щелкните правой кнопкой мыши и убедитесь, что они отформатированы как числа.
< /p>
Теперь введите свои формулы:
Функция CONVERT
Того же результата можно добиться с помощью встроенной в Excel функции ПРЕОБРАЗОВАТЬ. Убедитесь, что ваши ячейки времени/десятичных чисел отформатированы так же, как указано выше.
Теперь введите свои формулы:
Формальный способ
Сначала создайте новый столбец и введите формулу =ЧАС()+МИНУТА()/60. В моем случае это ячейка A2.
Также необходимо убедиться, что ячейки имеют правильный формат. Выберите столбец «Время», щелкните правой кнопкой мыши и выберите «Формат ячеек» и выберите «Пользовательский ч: мм».
< /p>
Проделайте то же самое для столбца "Десятичные числа", но установите для него число с двумя десятичными знаками.
< /p>
Теперь у вас будет время в десятичных дробях.
< /p>
Если этот блог помог вам, поделитесь им, потому что он также может быть полезен вашим друзьям.
Чтобы преобразовать часы в десятичном формате в допустимое время Excel, разделите их на 24. В показанном примере формула в C5 выглядит следующим образом:
который возвращает 0,04167, что эквивалентно 1 часу. Ячейка D6 показывает тот же результат, отформатированный как время, которое отображает 1:00.
В системе дат Excel один день равен 1, поэтому время можно рассматривать как дробные значения от 1, как показано в таблице ниже:
| Часы | Дробь | Значение | Время |
|---|---|---|---|
| 1 | 1/24 | 0,04167 | 1:00 |
| 3 | 3/24 | 0,125 | 3:00 |
| 6 | < td>6/240,25 | 6:00 | |
| 4 | 4/24 | 0,167 | 4:00 |
| 8 | 8/24 | 0,333< /td> | 8:00 |
| 12 | 12/24 | 0,5 | 12 :00 |
| 18 | 18/24 | 0,75 | 18:00 | < /tr>
| 21 | 21/24 | 0,875 | 21:00 |