Excel Разделит общий итог диапазона дат на ежедневные итоги

#excel #datetime #excel-formula #split #dataset

#преуспеть #дата и время #excel-формула #разделить #набор данных

Вопрос:

Я борюсь с формулой, я попробовал формулы сопоставления индексов с суммой, но это вообще не сработало.

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

Дата 24 часа длится с 07:00 до 07:00, поэтому для a она должна вычислять первый день с 2021/01/01 07:00 по 2021/01/02 07:00, любое время за пределами этого диапазона не должно добавляться к сумме.

Я создал пример ниже:

Любая помощь будет оценена.

1. Я думаю, вам было бы проще разработать решение для VBA или Power Query. Поскольку это не бесплатная служба кодирования, отредактируйте свой вопрос, чтобы показать ваши усилия и любые вопросы, которые могут возникнуть в ваших попытках решить эту проблему.

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

3. Мои извинения, джентльмены, я попробовал несколько попыток, но у меня не было времени опубликовать все мои попытки, @TomSharpe, ваше решение работает, и я изменил его, чтобы оно работало и в другое время. Я проголосую и одобрю как правильный ответ, спасибо

Ответ №1:

Это вариант стандартной формулы перекрытия

 =MAX(MIN(end1,end2)-MAX(start1,start2) 1,0)

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

 =SUM((A$2:A$8=I2)*TEXT(IF(C$2:C$8<(H2 31/24),C$2:C$8,H2 31/24)-IF(B$2:B$8>(H2 7/24),B$2:B$8,H2 7/24),"general;")*24)