000818.ru

Финансовые и Бух. услуги
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как округлить время до ближайшего часа / минуты / секунды в Excel?

Excel works!

menu

Еще раз привет. Как обычно начну статью с того, что скажу — часто встречаюсь с каким-нибудь незнанием со стороны пользователей и сейчас расскажу об этом. Банально, но действительно так и приходят идеи о статье. Сегодня хочется написать отдельный текст про работу со временем в Excel. Как можно быстро обработать временные данные? Как перевести время в число в Excel? Как не запутаться в этом — читаем в тексте!

перевести время в число в Excel

Время и число в Excel. =СЕГОДНЯ()

Сразу скажу что дата и время в Excel — это в первую очередь числовые данные. И только в определенном формате эти числа выглядят как время или дата.

Изначально у создателей Excel был небольшой выбор, как оформить время и данные в редакторе таблиц. Вопрос заключался в том, создавать ли отдельный тип данных для времени и даты или преобразовать их из числа в нужную форму отображения. На этот вопрос был дан быстрый и правильный ответ.

Любое значение дата_время в Excel — это число преобразованное в нужный формат числа при помощи опции Формат ячеек!

Приведем пример. Вам нужно задать сегодняшнюю дату? Пожалуйста! Записываем функцию =СЕГОДНЯ() — без аргумента в скобках. Она возвращает сегодняшнее число. Как правило, сразу в этой ячейке будет дата в правильном формате.

Время дата1

Но что произойдет, если данные будут преобразованы в цифровой формат? Появляется номер 42544.00.

Тоже самое будет и со временем в формате Время (см. на первой картинке), значение будет равно, например 21:00. Если сравнить значения чисел, то они будут идентичны.

Как же складывать и вычитать такие данные? Так же, как и обычные числа! Чтобы отобразить дату_время в ячейке, просто введите числа и выберите нужный формат. Как в примере:

Читайте так же:
Как объединить данные из нескольких листов / книг?

перевести время в число в Excel 1

То же самое верно при вычитании данных друг из друга.

Теперь можно перейти к хитростям работы с датами и временем.

Отделить дату от времени и наоборот в Excel

Предположим, что у вас уже есть цифры в формате дата-время, например, вы загрузили их из вашей бухгалтерской системы или прислали по почте.

Сначала проверьте, что это номер. После этого вы можете выполнить следующие действия. В противном случае следуйте инструкциям в статье.

Используйте формулу для определения времени из даты_времени

Формула находит целое значение числа, что будет для нас датой и вычитает из общего значения — разница будет для нас временем, не забудьте перевести в полученные данные в формат времени.

Время дата5

Как перевести время в число в Excel? Функции ДЕНЬ, ЧАС, МИНУТЫ

Это тоже просто, если знать хитрость. Очень, очень часто нам удобнее использовать формат 3,5 часа, чем 3:30. То есть, преобразовать число в формате времени в число, но с правильным значением. Для этого существуют отличные функции Excel =Час(), =Минуты().

Чтобы преобразовать 3:30 в три с половиной часа, нам потребуется формула.

Как это работает: вы находите количество полных часов, используя функцию HOUR, затем находите количество полных минут и делите на 60, чтобы получить минуты часа.

Так же, как верно подсказали мне в комментариях, можно умножить 3:30 (в ячейке A1) на 24 — получится числовое значение времени.

Если вы хотите подсчитать количество дней во времени (например, для 33:30:00 необходимо использовать функцию DAY).

Важно! Не забудьте перевести ячейку в числовой формат, иначе после выполнения формулы, или в ячейке будет выдаваться 0:00:00
Это будет удобно при работе с большим объемом данных, вычислениями со временем или группировкой данных в сводной таблице

Что такое округление?

Округление — это математическая операция, которая уменьшает количество десятичных знаков числа, заменяя число приближенным к его точности.

Читайте так же:
Как ограничить количество строк и столбцов на листе в Excel?

Вы можете округлять различными способами, например, округлять в большую сторону, округлять в меньшую сторону, округлять по модулю, округлять по модулю в меньшую сторону, случайное округление, чередующееся округление, округление ненулевых значений и банковское округление.

Наиболее распространенным способом является математическое округление, при котором число округляется вниз, если «отбрасываемое» число меньше пяти, и округляется вверх, если «отбрасываемое» число больше или равно пяти.

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

Давайте скомпилируем вышеприведенные знания в функции Python.

Сейчас мы рассмотрим встроенный модуль, который позволяет нам конвертировать секунды в нужный нам формат всего одной строкой кода.

Модуль времени определяет эпоху как 1 января 1970 года, 00:00:00 (UTC) в системах Unix (зависит от системы). Эпоха — это, по сути, начало времени для компьютера. Считайте ее этажом 0. Всякий раз, когда мы конвертируем секунды с помощью модуля времени, эта эпоха используется в качестве точки отсчета.

Используйте следующую строку кода, чтобы отобразить эпоху в вашей системе:

Time Epoch

Чтобы преобразовать секунды в предпочтительный формат, воспользуйтесь следующим кодом:

В этой строке время в секундах представлено символом «n», а часы, минуты и секунды выводятся по отдельности.

Полный код Python можно найти здесь:

Модули времени также позволяют отображать такую информацию, как день, месяц и год.

% аОтображать сокращенное название дня недели.
% АОтображать полное название дня недели.
% bОтображать сокращенное название месяца.
% BОтображать полное название месяца.
% cОтобразить соответствующее представление даты и времени.
% dОтображать день месяца как десятичное число [01,31].

Попробуем использовать% a и % b.

ОКРУГЛТ

Функция OKRUGLT очень полезна. Его можно использовать для округления не только до нужной цифры (слева или справа от десятичной точки), но и до нужной точности (кортеж) в целом. Например, вы хотите рассчитать заказ товара в штуках, но так, чтобы он был равен общему количеству коробок. Если в ящике 6 штук, то заказанное количество должно быть кратно 6. Или другой пример. Вы хотите, чтобы рассчитанная цена в итоге составила 00 или 50, которые кратны 50 рублям. Функция OKRUGLT легко решает такие проблемы.

Читайте так же:
Как объединить одноименные листы в один лист?

Округление с заданной кратностью

Очень, очень правильная функция. Это позволяет избежать 3 промежуточных вычислений (деление на кратное, округление до целого числа и снова умножение на кратное, как я делал сам, пока не обнаружил эту формулу).

Пояснение

Время, пересекающее полночь, затрудняет расчет количества часов между двумя точками во времени. Это связано с тем, что время, пересекающее полночь, часто имеет время начала позже времени окончания (например, начало в 21:00, конец в 6:00). Существует несколько различных решений этой проблемы, в зависимости от ситуации.

Простой расчет продолжительности

Когда время начала и время окончания совпадают в один и тот же день, вычислить продолжительность в часах несложно. Например, при времени начала 9:00 и времени окончания 17:00 вы можете просто использовать эту формулу:

Для отображения результата в часах и минутах используйте следующий формат времени:

Однако, когда часы пересекают полуночную отметку, все может стать сложнее.

Как Excel отслеживает время

В Excel день равен 1, что соответствует 24 часам. Это означает, что время и часы являются долями единицы, как показано в таблице ниже:

ЧасыВремяДоляЗначение
33:003/240,125
66:006/240,25
44:004/240,167
88:008/240,333
1212:0012/240,5
1818:0018/240,75
2121:0021/240,875

Когда наступает полночь

Вычислить прошедшее время сложнее, если время пересекает границу суток (полночь). Например, если в один день время начала — 22:00, а время окончания — 5:00 утра следующего дня, то время окончания на самом деле меньше, чем время начала, и приведенная выше формула вернет отрицательное значение, а Excel отобразит строку хеш-символов (########).
Чтобы исправить эту проблему, вы можете использовать эту формулу для времени, которое проходит границу суток:

Вычитая время начала из 1, вы получаете количество времени в день 1. Это время легко прибавить к времени окончания дня 2. Для случаев, которые происходят в один и тот же день, нам нужно использовать функцию ЕСЛИ, которая выглядит следующим образом:

Если оба времени относятся к одному и тому же дню, а конечное время больше начального, то используется простая формула. Но когда время пересекает границу дня, используется вторая формула.

Альтернатива c функцией ОСТАТ

Чтобы упростить приведенную выше формулу, OSTAT является элегантным решением. Применяя функцию OSTAT к делителю 1, мы можем создать формулу для обеих ситуаций:

Эта формула учитывает отрицательное время, используя функцию ОСТАТ, чтобы «перевернуть» отрицательное значение на требуемое положительное значение. Поскольку эта формула обрабатывает время в один и тот же день и время, после полуночи, то нам не нужен условный оператор ЕСЛИ.
Примечание: ни одна из приведенных выше формул не будет обрабатывать длительность более 24 часов.
Если вам это нужно, см. параметр «дата + время» ниже.

Упрощение с помощью даты + времени

Вы можете просто решить задачу радикального вычисления прошедшего времени, используя значения, содержащие как дату, так и время. Чтобы ввести вместе дату и время, используйте единый интервал между датой и временем.
Например. Если вы отформатируете эту дату 01.04.2020 10:00 в основном формате, вы увидите такое значение:

Числа справа от десятичной дроби представляют собой временную составляющую. Расчет прошедшего времени может быть выполнен на основе базовой формулы, когда у вас есть значения «дата и время». На приведенной ниже диаграмме начальное и конечное значения содержат дату и время, и формула проста:

Excel упрощенный расчет часов между двумя значениями времени
Результат отформатирован в пользовательском формате: [h]:mm для отображения прошедших часов. Эта формула правильно рассчитает часы между двумя временными метками за один день или за несколько дней.
Формат времени [h]:mm

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector