Excel дата и время формула округления значения ячейки

Question

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

Формула, которую я использую для вычисления разницы между временем начала и временем окончания каждого резервирования, (=TEXT[end_time]-[start_time],"h:mm") , отлично работает, но у меня есть проблема. Время окончания для бронирования указано как 00:29, когда заканчивается полчаса, и 00:59, когда заканчивается час.

У меня в таблице несколько тысяч строк, и я не хочу использовать Find & Replace, чтобы изменить эти значения на 00:30 и 00:00. Есть ли способ изменить мою формулу так, чтобы она округляла время окончания или, возможно, добавляла одну минуту ко времени окончания в формуле.

Answer 1

Редактировать на основе комментария Иззи:

end_time - start_time + TIME(0,1,0)

Документация для функции TIME()

Оригинальный ответ:

Вы можете добавить 1 минуту в расчете end_time - start_time, поняв, что в Excel 1 день равен целому числу 1, поэтому ваша формула будет выглядеть примерно так:

end_time - start_time + 1/24/60

(Деление на 24 часа и 60 минут).

Answer 2

Вы могли бы сделать это без математики. Для времени начала в А1 и времени окончания в В2:

=SUBSTITUTE(SUBSTITUTE(TEXT(B1-A1,"h:mm"),":29",":30"),":59",":00")

ЗАМЕНА - довольно простая функция подстановки текста. Требуется три параметра:

1. Первоначальный текст
2. Строка для замены.
3. Заменяющая струна.

ЗАМЕНА заменяет текст только при наличии совпадения с параметром 2, а затем заменяет только совпадающий текст - остальная часть строки остается одна, а строки, у которых вообще нет совпадения, остаются без изменений.

Итак, мы начнем выше с первоначального расчета времени из вашего вопроса. Это передается в ЗАМЕНУ, которая проверяет «:29» и заменяет «:30». (Если «:29» не найдено, оно ничего не делает.) Затем полученная строка передается внешнему SUBSTITUTE, который проверяет на «:59» и заменяет на «:00» при необходимости.

Answer 3

Если ваше время начала и окончания указано точно в часах и получасе, ваши формулы не должны возвращать продолжительность 29 или 59 минут. Я ожидаю, что экспорт базы данных также может включать несколько секунд времени. Мы можем полностью исключить секунды и вычислить длительности, которые предполагают начало / конец, равны нулю секунд в каждую минуту, например:

(Начало в А1. Конец в B1)

=TEXT(TIME(HOUR(B1),MINUTE(B1),0)-TIME(HOUR(A1),MINUTE(A1),0),"h:mm")

ВРЕМЯ - это функция, которая генерирует "серийный номер" Excel с использованием заданных часов, минут и секунд, а затем отображает его в стандартном формате времени. "Серийные номера" - это то, как Excel хранит значения даты / времени на сервере. Когда вы видите что-то вроде 8/1/2014 15:26:57 в ячейке, которая была настроена для форматирования даты / времени, Excel фактически сохраняет значение как 41852.64372 - это представляет количество дней (с десятичной частью, являющейся дробью одного дня) примерно с 1/1/1900 0:00:00 (это немного сложнее, но это уже больше, чем нужно знать).

HOUR и MINUTE - это функции (да, есть SECOND - и DAY, MONTH и YEAR, если они вам нужны), которые возвращают свои соответствующие части из серийного номера даты / времени.

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