Некоторое время назад для меня остро встал вопрос в корректировании полученного отчета в программе Excel. Суть в следующем: мы имеем отчет об этапах проделанных операций в определенную дату и время.
Выглядят ячейки так (пример):
Поступление операции: 15.05.2019 07:32:48
Время начала исполнения: 15.05.2019 07:48:12
Время завершение: 15.05.2019 08:01:05
Необходимо откорректировать эти данные так, что бы все операции происходили не позднее 4 утра и не раньше 22 вечера.
Отчет большой, иногда переваливающий за пару десятков страниц. Делается он ежемесячно, и просматривать каждую ячейку вручную крайне трудозатратно и велика вероятность пропустить то или иное значение, всё таки все мы не идеальны.
Я далеко не программист, в экселе я совсем не герой. Но острое желание получить достойный результат за короткое время превалировал. Некое подобие решения я нашел, чем и хочу поделиться с читателями. Возможно кому-то это пригодится. формула выглядит следующим образом:
=ТЕКСТ(A1; "дд.ММ.гггг")&" "&ТЕКСТ(ЕСЛИ(И(ЧАС(A1)>ЧАС(0,2); ЧАС(A1)<ЧАС(0,5));ЧАС(0,2);ЕСЛИ(И(ЧАС(A1)>=ЧАС(0,5);ЧАС(A1)<=ЧАС(0,95));ЧАС(0,95);ЧАС(A1))); "??")&":"&ТЕКСТ(A1; "мм:сс")
Здесь в ячейке "А1" указанно исходное значение в пользовательском формате "дата время". Формула делает следующее: переводит данные в текстовый формат, а так же создает условия, что если часы в исходной ячейке находятся в диапазоне до 12 часов и при этом больше 4 утра, то часы становятся равными 4 утра, и, если диапазон находится после 12 и при этом являются менее 22 часов вечера, часы становятся равными 22.
Почему в формуле используется "ЧАС(0,2)" или "ЧАС(0,95)" вместо "4" или "22", потому что, если поставить "4", то в конечном результате получится что-то типа "0:48:23", т.е. вместо "4" часов ексель выдаст "0". Почему так, я не знаю. В общем-то остается только заменить значения в исходных ячейках на полученные.
Но далее встает следующий вопрос: если со "времени начала операции" и до "времени завершения операции" сменился час, например: 22:57:12 -> 23:02:27, то формула в любом случае округлит второе значение до 22 часов. и в итоге получится, что мы начали операцию позже, чем ее закончили. Автоматизировать данное условия я уже не смог. Вышел из положения следующим образом: создал рядом столбик с формулой
=ЕСЛИ(И(ЧАС(A2)=ЧАС(B2); МИНУТЫ(A2)>МИНУТЫ(B2));1;0)
В случае, если значение было равно 1, то вручную менял в первой формуле значение часов в зависимости от того до обеда это дело происходило или после.
Я понимаю, что решение совершенно не элегантное, и, на глаз профессионального программиста - это работа топором и лопатой, но уж как смог. :)
P.S. Если кто-то мне подскажет более надежное решение, с большим функционалом автоматизации, осыплю лучами доброты и благодарности.