Как вы округлите время и дату до предыдущего дня с формулой в Excel?

Question

Я пытаюсь подвести итоги рабочего времени работника в одну смену. Сотрудники, с которыми у меня проблемы, работают в ночную смену. Записи сотрудников в Excel отображаются в виде отдельных билетов на проделанную работу. Допустим, сотрудник начинает билет в 23:00 и заканчивает его в 2:30, затем следующий билет начинается в 2:30 и заканчивается в 5:00. Мне нужны эти 2 билета, чтобы показать введенные часы для даты первого билета смены, а не на следующий день. Мне нужна формула, которая могла бы работать с несколькими сотрудниками в течение месяца работы в две смены: день с 5:45 до 18:00 и ночь с 5:45 до 6:00.

Мои столбцы:

• А) Номер билета
• Б) Имя сотрудника
• C) Общее количество билетов [4.5]
• D) Время начала [5/5/2018 22:00]
• E) Время окончания [4/6/2018 2:30]

Я использую Office 365 Excel 2016

Answer 1

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

Первым делом я вставил новый столбец и скопировал в него данные D Start Time.

Затем я изменил формат нового столбца на Общий (это разбило время и дату до десятичных серийных номеров).

Наконец, я сделал еще один новый столбец и придумал эту формулу =[@Column2]-0.229 . Это отнимает 5 часов и 29 минут (0,229) от времени начала, которое, в свою очередь, отбрасывает билет обратно к первоначальной дате поступления.

Это также можно упростить, просто добавив новый столбец и используя формулу, вычитающую 0,229 из даты / времени в столбце D