Я действительно хотел бы прикрепить файлы
Если у кого-то есть решение для этого, дайте мне знать
У меня есть решение для вас. Сначала немного настроек:
Вы можете настроить ту же самую вещь с динамическими именованными диапазонами и использовать их в своих формулах, но мне нравятся таблицы, поэтому я использовал их.
Я создал таблицу в столбцах A:B с именем tblTimeLog
Имеет два поля: Старт, Стоп
Здесь вы вводите все время запуска и остановки по мере необходимости
Я создал вторую таблицу в столбцах D:G с именем tblTimeSummary
Имеет 4 поля: Интервальный взгляд, Интервал Стоп, Интервал, Общее время
Интервал Start & Stop - это удобство
Вы можете сделать все с одним текстовым полем, показывающим промежуток времени, но формулы будут намного проще, если вы разделите его
Эти два будут содержать диапазоны времени, такие как 07:00 и 07:30, 07:30 и 08:00 и т.д.
Я набрал их вручную и заполнил
Интервал просто объединяет два ради презентации, поэтому вы получаете такие вещи, как «07:00 - 07:30», «07:30 - 08:00» и т.д.
Я использовал здесь формулу: =TEXT([@[Interval Start]],"HH:mm") & " - " & TEXT([@[Interval Stop]],"HH:mm")
Общее время - это место, где будет рассчитываться объем работы, выполненной за этот интервал.
Вот большая формула для общего времени:
=SUMPRODUCT((tblTimeLog[Start]<=[@[Interval Stop]])*(tblTimeLog[Stop]>[@[Interval Start]])*(IF([@[Interval Stop]]<=tblTimeLog[Stop],[@[Interval Stop]],tblTimeLog[Stop])-IF([@[Interval Start]]>tblTimeLog[Start],[@[Interval Start]],tblTimeLog[Start])))*24*60
Давайте разберемся с этим
SUMPRODUCT создаст массивы, умножит каждую строку вместе, а затем суммирует эти строки
Важно знать, что это будет трактовать TRUE как 1 и FALSE как 0
Первый массив (tblTimeLog[Start]<=[@[Interval Stop]])
Это находит все записи журнала, которые начались до конца этого интервала
Второй массив - противоположная идея (tblTimeLog[Stop]>[@[Interval Start]])
Он находит все записи журнала, которые закончились после начала этого интервала
Вместе они находят все записи в журнале, которые имеют некоторое время в течение интервала
(IF([@[Interval Stop]]<=tblTimeLog[Stop],[@[Interval Stop]],tblTimeLog[Stop])
Этот массив выбирает либо конец интервала, либо конец действия, в зависимости от того, что было первым
IF([@[Interval Start]]>tblTimeLog[Start],[@[Interval Start]],tblTimeLog[Start])
Этот массив выбирает либо начало интервала, либо начало действия, в зависимости от того, что наступило последним
Разница между двумя последними массивами показывает, сколько времени каждая запись журнала была потрачена в этом конкретном интервале.
*24*60 этот бит в конце просто превращает значение времени в днях в значение в минутах
Подведем итоги с помощью некоторого псевдокода:
=Sum((If the log entry is in this interval)*(How much time was spent in this interval))
Первый массив будет 1 или 0, а второй будет некоторое значение времени
Умножение их вместе дает массив значений 0 и времени
Суммирование, которое дает вам общее зарегистрированное время в этом интервале
