Excel количество секунд в дату

history 22 мая 2022 г.

Группы статей
• ВРЕМЯ
• Пользовательский Формат ДАТЫ и ВРЕМЕНИ

EXCEL хранит ВРЕМЯ в числовой форме (в дробной части числа). Например, 1,0 соответствует 1 суткам, 0,5 — 12 часам. 

В ячейке с форматом Время 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? В этой статье я расскажу о некоторых хитростях для решения этой задачи, пожалуйста, прочтите следующие детали:

Преобразование количества секунд в формат времени с помощью формул

Преобразование формата времени чч:мм:сс в секунды/минуты/часы с Kutools for Excel

Преобразование количества секунд в формат времени с помощью формул

Предположим, у меня есть следующие числа секунд в столбце, чтобы преобразовать их в формат времени дни, часы, минуты, секунды, вы можете применить формулы:

1. Введите эту формулу: = A2 / 86400 в пустую ячейку, в которую вы хотите вывести результат, и перетащите дескриптор заполнения вниз к ячейкам, к которым вы хотите применить эту формулу, и вы получите несколько чисел, как показано на следующем снимке экрана:

2. Выделите эти ячейки формулы и щелкните правой кнопкой мыши, чтобы выбрать Формат ячеек вариант из контекстного меню, во всплывающем Формат ячеек диалоговое окно под Число вкладку нажмите На заказ вариант и введите дд: чч: мм: сс в Тип box, см. снимок экрана:

3, Затем нажмите OK кнопка, и ячейки были отображены в формате времени дд: чч: мм: сс, см. снимок экрана:

Внимание: Вот еще одна формула, которая также может помочь вам отобразить количество секунд в текстовом формате, например: x часов x минут x секунд: = ТЕКСТ (A2 / 86400; ВЫБРАТЬ (ПОИСКПОЗ (A2; {0,60,3600}; 1), «s» «sec» «», «m» «min» «s» «sec» «», «[ h] «» hrs «» m «» min «» s «» sec «» «)), после применения этой формулы вы получите такой результат, см. снимок экрана:

Преобразование формата времени чч:мм:сс в секунды/минуты/часы с Kutools for Excel

В отличие от вышеуказанной проблемы, если вы хотите преобразовать формат времени hh: mm: ss в секунды, минуты или часы, я могу порекомендовать полезный инструмент —Kutools for Excel, С его Конвертировать время функцию, вы можете быстро преобразовать формат времени в количество часов, минут или секунд.

После установки Kutools for Excel, пожалуйста, сделайте следующее:

1. Выберите время, которое вы хотите преобразовать.

2. Нажмите Кутулс > Содержание > Конвертировать время, а затем выберите Время до часов / Время до минут / Время до секунд как вам нужно, см. снимок экрана:

3. И выбранные вами ячейки времени были преобразованы в желаемые часы, минуты или секунды, которые вам нужны, см. Снимок экрана:

Внимание: Если вам не нравится преобразованный результат, расположенный в исходных ячейках, вы можете перейти к Конвертировать время диалоговое окно, нажав Кутулс > Содержание > Конвертировать время, В Конвертировать время диалоговом окне выберите нужный тип преобразования, а затем установите флажок Сохранить в другое место Установите флажок и щелкните ячейку, в которую вы хотите поместить результат, см. снимок экрана:

Затем нажмите OK кнопку, преобразованные результаты будут расположены в выбранных вами ячейках.

Скачать и бесплатную пробную версию Kutools for Excel Сейчас !