Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Расчет суммы часов в промежутках времени, Работа с временем и датой
 
#1
25.02.2019 10:08:01
Добрый день! Прошу помощи в вычислении времени. Есть таблица с колонкой Начало и Окончание в форматах Дата время, нужно посчитать сумму времени промежутков с 12:00 - 15:00 и 17:00 - 19:00.
В таблице пример:
первая строка время начала и конца не вошло в промежуток - значит 0 часов
вторая строка вошла в промежуток с 12 до 15, значит 3 часа
третья 3 строка вошла в оба промежутка значит простой 5 часов (с 12:00 - 15:00 и 17:00 - 19:00)
четвертая строка вошла в промежуток с 12 до 15 на 1 час 8 минут - значит записывает 1 час 8 минут.
Таблица очень большая, поэтому руками ее делать крайне долго, может кто подсказать как рассчитать такую формулу?
Заранее спасибо
НачалодатаЧасы началаОкончаниеДатаЧасы окончанияПростой с 12:00   - 15:00 и 17:00 - 19:00
02.01.2018   5:5602.01.20185:5602.01.2018   10:5702.01.201810:57:000:00:00
02.01.2018   6:1002.01.20186:1002.01.2018   15:5002.01.201815:50:003:00:00
02.01.2018   7:5902.01.20187:5902.01.2018   21:0702.01.201821:07:005:00:00
02.01.2018   8:2202.01.20188:2202.01.2018   13:0802.01.201813:08:001:08:00
Изменено: txi - 25.02.2019 10:23:54
 
 
 
#2
25.02.2019 10:16:54
файл где?
Иначе никто не поможет
 
 
 
#3
25.02.2019 10:24:41
Геннадий, приложил таблицу с частью строк
 
 
 
#4
25.02.2019 10:30:38
вот для последней строки примера, даты 02.01.2018  9:26:00  и 05.02.2018  9:02:00
здесь как считать?
 
 
 
#5
25.02.2019 11:10:34
Михаил Витальевич С., в идеале должно получиться 15 часов: 2,3,4 число по 5 часов
 
 
 
#6
25.02.2019 11:25:35
Я так понимаю, выходные/праздничные не учитываются?

упсс...
Цитата
txi написал:
2,3,4 число по 5 часов
а целый месяц с 05.01 по 05.02
Изменено: Михаил Витальевич С. - 25.02.2019 11:29:50
 
 
 
#7
25.02.2019 11:30:58
Михаил Витальевич С.,если возможно учесть выходные то прошу учесть. В выходные часы не должны учитываться вообще.
 
 
 
#8
25.02.2019 11:32:04
а если выходной был рабочим днем?
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
#9
25.02.2019 11:34:07
Цитата
Ігор Гончаренко написал:
а если выходной был рабочим днем?
Игорь, тогда нужен календарь рабочих дней :)  Логично же.
 
 
 
#10
25.02.2019 11:37:05
правильно.
задачу нужно сначала сформулировать, а потом можно и решать
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
#11
25.02.2019 11:38:28
Михаил Витальевич С. , тогда все дни с пн-пт по 5 часов но это не так критично на самом деле т.к. у меня все строки в пределах 1-2 дней
 
 
 
#12
25.02.2019 11:52:56
честно говоря, подобная задача решалась на форуме несколько раз...
поищите, может найдете готовое решение.
 
 
 
#13
25.02.2019 12:25:59
Накидал. Вроде работает как надо:
Код
=ЕСЛИ(ИЛИ(ЕСЛИ(ВРЕМЯ(ЧАС(A2);МИНУТЫ(A2);СЕКУНДЫ(A2))<=ВРЕМЯ(12;0;0);0;ЕСЛИ(ВРЕМЯ(ЧАС(A2);МИНУТЫ(A2);СЕКУНДЫ(A2))<=ВРЕМЯ(15;0;0);ВРЕМЯ(ЧАС(A2);МИНУТЫ(A2);СЕКУНДЫ(A2))-ВРЕМЯ(12;0;0);ЛОЖЬ))=ЛОЖЬ;ЕСЛИ(ВРЕМЯ(ЧАС(B2);МИНУТЫ(B2);СЕКУНДЫ(B2))>=ВРЕМЯ(15;0;0);0;ЕСЛИ(ВРЕМЯ(ЧАС(B2);МИНУТЫ(B2);СЕКУНДЫ(B2))<=ВРЕМЯ(12;0;0);ЛОЖЬ;ВРЕМЯ(15;0;0)-ВРЕМЯ(ЧАС(B2);МИНУТЫ(B2);СЕКУНДЫ(B2))))=ЛОЖЬ);0;ВРЕМЯ(3;0;0)-ЕСЛИ(ВРЕМЯ(ЧАС(A2);МИНУТЫ(A2);СЕКУНДЫ(A2))<=ВРЕМЯ(12;0;0);0;ЕСЛИ(ВРЕМЯ(ЧАС(A2);МИНУТЫ(A2);СЕКУНДЫ(A2))<=ВРЕМЯ(15;0;0);ВРЕМЯ(ЧАС(A2);МИНУТЫ(A2);СЕКУНДЫ(A2))-ВРЕМЯ(12;0;0);ЛОЖЬ))-ЕСЛИ(ВРЕМЯ(ЧАС(B2);МИНУТЫ(B2);СЕКУНДЫ(B2))>=ВРЕМЯ(15;0;0);0;ЕСЛИ(ВРЕМЯ(ЧАС(B2);МИНУТЫ(B2);СЕКУНДЫ(B2))<=ВРЕМЯ(12;0;0);ЛОЖЬ;ВРЕМЯ(15;0;0)-ВРЕМЯ(ЧАС(B2);МИНУТЫ(B2);СЕКУНДЫ(B2)))))+ЕСЛИ(ИЛИ(ЕСЛИ(ВРЕМЯ(ЧАС(A2);МИНУТЫ(A2);СЕКУНДЫ(A2))<=ВРЕМЯ(17;0;0);0;ЕСЛИ(ВРЕМЯ(ЧАС(A2);МИНУТЫ(A2);СЕКУНДЫ(A2))<=ВРЕМЯ(19;0;0);ВРЕМЯ(ЧАС(A2);МИНУТЫ(A2);СЕКУНДЫ(A2))-ВРЕМЯ(17;0;0);ЛОЖЬ))=ЛОЖЬ;ЕСЛИ(ВРЕМЯ(ЧАС(B2);МИНУТЫ(B2);СЕКУНДЫ(B2))>=ВРЕМЯ(19;0;0);0;ЕСЛИ(ВРЕМЯ(ЧАС(B2);МИНУТЫ(B2);СЕКУНДЫ(B2))<=ВРЕМЯ(17;0;0);ЛОЖЬ;ВРЕМЯ(19;0;0)-ВРЕМЯ(ЧАС(B2);МИНУТЫ(B2);СЕКУНДЫ(B2))))=ЛОЖЬ);0;ВРЕМЯ(2;0;0)-ЕСЛИ(ВРЕМЯ(ЧАС(A2);МИНУТЫ(A2);СЕКУНДЫ(A2))<=ВРЕМЯ(17;0;0);0;ЕСЛИ(ВРЕМЯ(ЧАС(A2);МИНУТЫ(A2);СЕКУНДЫ(A2))<=ВРЕМЯ(19;0;0);ВРЕМЯ(ЧАС(A2);МИНУТЫ(A2);СЕКУНДЫ(A2))-ВРЕМЯ(17;0;0);ЛОЖЬ))-ЕСЛИ(ВРЕМЯ(ЧАС(B2);МИНУТЫ(B2);СЕКУНДЫ(B2))>=ВРЕМЯ(19;0;0);0;ЕСЛИ(ВРЕМЯ(ЧАС(B2);МИНУТЫ(B2);СЕКУНДЫ(B2))<=ВРЕМЯ(17;0;0);ЛОЖЬ;ВРЕМЯ(19;0;0)-ВРЕМЯ(ЧАС(B2);МИНУТЫ(B2);СЕКУНДЫ(B2)))))+ВРЕМЯ(Ч(ДЕНЬ(B2)-ДЕНЬ(A2))*5;0;0)
Изменено: magistor8 - 25.02.2019 14:20:25
 
 
 
#14
25.02.2019 13:31:50
magistor8,Вау, я в восторге, но не на всех строчках работает например с такими данными выводит ########
02.01.18 17:0602.01.18   18:50
а тут 1:35 выводит хотя должно быть 2
02.01.2018 15:4502.01.2018   20:45
Изменено: txi - 25.02.2019 13:33:02
 
 
 
#15
25.02.2019 14:15:23
Поправил.
Правда конструкция чуть больше чем у Андрей VG
Изменено: magistor8 - 25.02.2019 14:22:09
 
 
 
#16
25.02.2019 14:18:03
Цитата
txi написал:
но не на всех строчках работает
Тогда начните с простой цепочки рассуждений о пересечении интервалов. А потом можно уже и в одну формулу завернуть.
Изменено: Андрей VG - 25.02.2019 15:05:07 (Перезагрузил файл. Ошибся на день.)
 
 
 
#17
26.02.2019 07:09:17
Спасибо всем большое!
 
 
 
Страницы: 1
Наверх