Преобразование в MS EXCEL секунд в сутки, часы и минуты
В ячейке с форматом Время 12 часов (т.е. число 0,5) отражаются как 12:00:00. Но, что если в ячейке содержится количество секунд, например, 100 000, и их нужно преобразовать в дни, часы и минуты?
Теория
Прежде чем начать решать задачу, вспомним, что в 1 сутках 24 часа, в 1 часе 60 минут, а в минуте 60 секунд, т.е. в сутках 24*60*60=86400 секунд. Также вспомним как EXCEL обращается с датами и временами: Excel хранит даты и время в виде чисел, а вот отображать может по разному:
1. Временные значения (числа меньше 1) в формате ячейки могут отображаться как 13:30:25 (чч:мм:сс). Чтобы отобразить 1 секунду в формате 00:00:01 нужно ввести в ячейку значение =1/86400 и установить формат ячеек чч:мм:сс. 1 минута — это =1/24/60. Про временные форматы, в том числе и нестандартные — пользовательские, подробнее читайте здесь.
2. Значения Даты (целые числа больше 1) стандартно отображаются форматом ячейки как 01.02.1988 или 1 февраля, например. Числовые значения с дробной частью интерпретируются EXCEL как содержащие дату и время. Например, число 2,45 — это 02.01.1900 10:48:00. 2 января 1900 года — это число 2, т.к. отсчет дат ведется в MS EXCEL начиная с 01.01.1900. 10:48 получилось из 0,45 так: т.к. 1 — это 24 часа, т.е. 1440 минут, то 0,45 — это 0,45*1440=648 минут=10*60мин+48 мин=10 частов 48 мин.
3. Отображаться даты и времена могут и в текстовом формате, визуально не отличимым от вышеперечисленных форматов. Это реализуется, например с помощью функции ТЕКСТ. Например, формула =ТЕКСТ(1,3;»ч:мм») вернет текстовую строку 7:12. Такой формат удобен при составлении текстовых строк, содержащих даты и время. Но, дальнейшие вычисления с временами и датами в текстовом формате не возможны.
4. Произвольный формат, удобный пользователю. Вышеперечисленные 3 формата отображения могут отобразить секунды и минуты от 0 до 59, а часы от 0 до 23. Даты будут отображены календарные — от 1 до 31 максимум. Если попытаться ввести 145 минут, например в функции ВРЕМЯ, то формула =ВРЕМЯ(;145;) вернет 2:25:00, т.е. 2 часа 25 минут = 2*60+25=145 минут. Однако, есть задачи, когда дано определенное количество секунд или минут и их нужно преобразовать в стандартный для MS EXCEL формат.
Решение задачи
Чтобы перевести 100 000 секунд в стандартный формат времени и даты разделим 100000 на 86400 (секунд в сутках), получим примерно 1,157407, т.е. больше 1 суток. Стандартный формат ячейки Время отобразит 3:46:40 (ч:мм:сс), т.е. количество дней не будет отражено. В EXCEL есть еще один стандартный формат в группе Дата, который отображает и даты и время одновременно.
В итоге будем иметь 01.01.00 3:46 (нет секунд и вместо количества суток (1) отображается 1 января 1900 года). Нажав внизу левого списка значение (все форматы) получим явный формат, который был применен ДД.ММ.ГГ ч:мм;@
Отредактируем его как Д \д ч:мм:сс;@ чтобы отображались и секунды и день. Получим 1 д 3:46:40, т.е. то что мы хотели. Вместо символа «д» можно указать сутки, откорректировав формат Д \суток ч:мм:сс. Все будет работать хорошо пока количество суток не превысит 31. Это произойдет для 2 764 799 секунд,что соответвует 31 дню и 23:59:59 . Это происходит, когда чуть большее количество секунд дает дату 01.02.1900, т.е. вместо 32 суток отображается день этой даты, т.е. 1. Нам так не нужно. Поэтому, придется написать нестандартный пользовательский формат. Например, попробуем сделать 1:3:46:40. Проще всего сделать форматирование с помощью текстовой строки, получаемой формулой =ЦЕЛОЕ(1/24/60/60*H4)&»:»&ТЕКСТ(H4/(24*60*60);»ч:мм:сс»)
Другой вариант — отображение суток, часов, минут и секунд в отдельных ячейках.
В файле примера создана форма для перевода значения секунд в целое количество суток, часов, минут и секунд. Эта форма универсальная — можно ввести и минуты и часы и сутки. Формулы работают даже для чисел с дробной частью. Например, 1,5 суток будут преобразованы в 1 сутки и 12 часов.
Часть формул используют таблицу преобразования сутки-часы-минуты-секунды:
Для перфекционистов часть формул даже правильно склоняют слово «день».
Как перевести число в часы и минуты в excel
Подскажите, есть ли возможность в Excel отобразить число 120 как 2:00 или 58 в 0:58 и т.д. ?
т.е. перевести число минут в формат чч:мм.
Пользователь
Сообщений: 3102 Регистрация: 24.12.2012
06.04.2012 10:38:16
=ТЕКСТ(A1/60/24;»чч:мм») — в виде текста
=—ТЕКСТ(A1/60/24;»чч:мм») — в виде числа с которым можно работать как со временем (поставить формат времени для ячейки)
Редко но метко .
06.04.2012 10:38:42
разделить на 1440 и формат времени
06.04.2012 12:51:25
Большое спасибо )
Страницы: 1
Читают тему
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
| ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949 |
ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071 |
Преобразование времени
Предположим, что вы хотите узнать, сколько часов в 5,5 дней, сколько дней через 3,45 года и как точно будет проходить количество месяцев между датой начала и конечной датой.
Преобразовать единицы времени можно несколькими способами.
Преобразование между единицами времени
Для преобразования единицы времени в другую используйте функцию ПРЕОФОРМ.
Примечание: Для преобразований, в которые включается год, следует учесть, что год составляет 365,25 дня.
Преобразование стандартного формата времени в десятичной формат
Для преобразования из формата часы : минуты : секунды используйте функцию INT.
Преобразование десятичных форматов в стандартный формат времени
Чтобы преобразовать десятичной формат в часы: минуты : секунды, используйте оператор делетеля (/) вместе с функцией ТЕКСТ.
Примечание: Формат времени также можно применить без указания формата с помощью функции ТЕКСТ. Чтобы просмотреть число как время, выйдите из ячейки и на вкладке Главная в группе Число щелкните стрелку рядом с полем Числовом формате. Затем выберите Другие числовые форматы, в списке Категория выберите другой, а затем в поле Тип выберите пользовательский формат. Имейте в виду, что если вы используете как формат, примененный к функции ТЕКСТ, так и числовом формате, функция ТЕКСТ имеет приоритет.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
Преобразовать целое число в час
Как преобразовать целое число в час в Excel? Это одна из наиболее распространенных проблем в вычислительной технике, и не только в Excel.
Мы увидим, как выполнить эту задачу тремя разными способами.
- С математической формулой
- С функцией ВРЕМЯ
- Использование специального клея.
Откуда возникает проблема?
Часы ВСЕГДА составляют долю суток. Например
- 0,5 равно 12 часам (полдня)
- 0,33333 = 8 часов (1/3 суток).
Вы можете проверить это, изменив формат десятичного числа на формат времени.
Когда вы пишете часы целыми числами, для Excel это не часы, а дни.
Метод 1: Математический расчет
Поскольку в сутках 24 часа, просто разделите полученные значения на 24.
И когда мы помещаем эти числа в формат времени. ВОЛШЕБСТВО. Теперь данные в формате времени⏰
Метод преобразования целого числа в часы работает, но может оказаться утомительным. Особенно, если вам нужно преобразовать много ячеек.
Перевести в минуты
Теперь, если цифры в ячейках соответствуют минутам, например 90 или 120, на этот раз вам нужно разделить на 24*60 (24 часа, а также 60 минут в часе)
Использование круглых скобок здесь обязательно для соблюдения математические приоритеты. Без скобок расчет будет неправильным, поскольку сначала формула будет вычислять ячейка/24, то этот результат будет умножить на 60.
Применяя формат времени, результат
Способ 2: функция ВРЕМЯ
Другой метод заключается в использовании функция ВРЕМЯ чтобы сделать преобразование. Но Функция ВРЕМЯ не интерпретирует ЧТО вся часть ваших данных.
Пример с часами
Пример с минутами
Способ 3: специальная паста
Это самый простой и наименее известный метод.
В следующем видео объясняется, как использовать специальный коллаж для преобразования целых чисел в часы.
Оставить комментарий Отменить ответ
Этот сайт использует Akismet для уменьшения нежелательности. Узнайте больше о том, как используются ваши комментарии.