Я хочу рассчитать количество рабочих часов между 2 днями.
Например,
A B
15/05/2013 13:25 31/05/2013 10:20
19/05/2013 09:12 22/05/2013 21:10
Я хочу получить результат в столбце C с возможным следующим сроком.
6d 15h 20m
или что-нибудь подобное выше. Пожалуйста помоги.
Решение представлено на этом сайте, и оно включает в себя довольно сложную формулу:
=IF(AND(INT(StartDT)=INT(EndDT),NOT(ISNA(MATCH(INT(StartDT),
HolidayList,0)))),"0 days 0 hours", IF(INT(StartDT)=INT(EndDT),
"0 days " & ROUND(24*(EndDT-StartDT),2)&"hours",
MAX(NETWORKDAYS(StartDT+1,EndDT-1,HolidayList),0)+
INT(24*(((EndDT-INT(EndDT))-(StartDT-INT(StartDT)))+
(DayEnd-DayStart))/(24*(DayEnd-DayStart)))&
" days "&MOD(ROUND(((24*(EndDT-INT(EndDT)))-24*DayStart)+
(24*DayEnd-(24*(StartDT-INT(StartDT)))),2),
ROUND((24*(DayEnd-DayStart)),2))&" hours "))
Предполагает следующие имена:
StartDT - дата и время начала (25-Oct-99 13:00)EndDT - конечная дата и время (28-Oct-99 15:00)DayStart - начало рабочего дня (9:00)DayEnd - конец рабочего дня (17:00)HolidayList - диапазон, содержащий список дат для исключенияЯ не могу проверить это, так как у меня нет Excel, но он должен выполнить свою работу. Конечно, вы можете изменить формулу для достижения другого результата.
Вы не можете получить желаемый результат в одной ячейке, но вы можете рассчитать продолжительность в C1 (при условии, что вы находитесь в строке 1), используя =ABS(B1-A1) . Это даст вам количество дней в качестве значения с плавающей запятой. Затем вы можете использовать другие ячейки и формулы, чтобы разделить это число на часы, минуты и секунды, используя ROUNDDOWN() для вычисления целых чисел.
Редактировать: вот содержимое моей примерной электронной таблицы, так как я не могу прикрепить ее здесь, и я не знаю, каковы политики для ссылки на файлы, которые могут быть или не быть в будущем. Это рассчитывает все время в продолжительности, а не только с 09:00 до 17:00 M – F. Ответ @ slhck может быть более подходящим.
Строка 1:(A) Start (B) Stop (C) Days (D) Hours (E) Minutes (F) Seconds
Строка 2:(A) 5/1/2013 17:55:00 (B) 6/3/2013 1:22:00 (C) =ROUNDDOWN(ABS(B2-A2)) (D) =ROUNDDOWN(((ABS(B2-A2))-C2)*24) (E) =ROUNDDOWN(((((ABS(B2-A2))-C2)*24)-D2)*60) (F) =((((((ABS(B2-A2))-C2)*24)-D2)*60)-E2)*60