Из общего времени - ночные

Дата: Вторник, 06.11.2012, 18:21 | Сообщение № 1

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 142

Репутация: 0 ±

Замечаний: 40% ±

2010

Как с помощью формулы найти время работ в ночное время начало работ

К сообщению приложен файл: __.xlsx (12.7 Kb)

Gustav

Дата: Вторник, 06.11.2012, 18:50 | Сообщение № 2

Группа: Админы

Ранг: Участник клуба

Сообщений: 2829

Репутация: 1190 ±

Замечаний: ±

начинал с Excel 4.0, видел 2.1

Например, так для ячейки L6 (пробел перед второй ДВССЫЛ обязателен):

[vba]

Code

=(СЧИТАТЬПУСТОТЫ(
ДВССЫЛ("A"&ЦЕЛОЕ(1440*G6)&":A"&ЦЕЛОЕ(1440*(1+I6)))
ДВССЫЛ("A"&ЦЕЛОЕ(1440*22/24)&":A"&ЦЕЛОЕ(1440*(1+6/24)))
)-1)/1440

[/vba]

Формула предполагает, что:
* столбец A - пустой; во всяком случае, начиная с ячейки примерно A500. Если это невозможно обеспечить, то вместо "A" следует использовать подходящий пустой столбец, например, "IV";
* максимальная продолжительность одной работы ограничена двумя соседними сутками: "сегодня" и "завтра".

Некоторые пояснения по примененному подходу, связанному с отображением временнЫх интервалов на "шкалу" диапазонов Excel, можно получить отсюда: http://www.excelworld.ru/forum/7-2537-28184-16-1350038688 . Решение получается путем нахождения пересечения общего диапазона работы с ночным диапазоном (с точностью до минуты, 1440 - кол-во минут в сутках).

P.S. "Когда верстался номер" - надумалась формула, свободная от пустоты столбца А:

[vba]

Code

=(ЧСТРОК(
ДВССЫЛ(ОКРУГЛ(1440*G6;)&":"&ОКРУГЛ(1440*(1+I6);))
ДВССЫЛ(ОКРУГЛ(1440*22/24;)&":"&ОКРУГЛ(1440*(1+6/24);))
)-1)/1440

[/vba]

К сообщению приложен файл: andron359_03.xlsx (13.2 Kb)

МОИ: Ник, Tip box: 41001663842605

Сообщение отредактировал Gustav - Вторник, 06.11.2012, 19:40

Дата: Вторник, 06.11.2012, 19:04 | Сообщение № 3

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 142

Репутация: 0 ±

Замечаний: 40% ±

2010

Gustav, Спасибо

Дата: Вторник, 06.11.2012, 21:12 | Сообщение № 4

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 142

Репутация: 0 ±

Замечаний: 40% ±

2010

Gustav, Если день один и тот же то не считает , можете поправить

К сообщению приложен файл: 4232216.xlsx (12.8 Kb)

Дата: Среда, 07.11.2012, 01:04 | Сообщение № 5

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4517

Репутация: 1129 ±

Замечаний: 0% ±

Excel 2003

Формула для L6

Code

=МАКС(0;6/24-G6)+МИН(2/24;1-G6)+МИН(6/24;I6)+МИН(2/24;1-I6)+(H6-F6-1)*8/24

К сообщению приложен файл: nt_AlexM.xls (31.5 Kb)

Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.

Gustav

Дата: Среда, 07.11.2012, 10:08 | Сообщение № 6

Группа: Админы

Ранг: Участник клуба

Сообщений: 2829

Репутация: 1190 ±

Замечаний: ±

начинал с Excel 4.0, видел 2.1

Quote (andron359)

Если день один и тот же то не считает , можете поправить

Да, что-то я кардинально недоучёл этот момент... На протяжении двух суток надо же учитывать аж три ночных периода: с 0 ч до 6 ч первых суток, с 22 ч первых до 6 ч вторых и с 22 ч до 24 ч вторых. Что ж, формула заметно, но, надеюсь, теперь правильно потяжелела:

[vba]

Code

=ЕСЛИОШИБКА(ЧСТРОК(ДВССЫЛ(ОКРУГЛ(1440*G6+1;)&":"&ОКРУГЛ(1440*(H6-F6+I6);)) ДВССЫЛ(ОКРУГЛ(1440*0/24+1;)&":"&ОКРУГЛ(1440*6/24;)))/1440;0)
+ЕСЛИОШИБКА(ЧСТРОК(ДВССЫЛ(ОКРУГЛ(1440*G6+1;)&":"&ОКРУГЛ(1440*(H6-F6+I6);)) ДВССЫЛ(ОКРУГЛ(1440*22/24+1;)&":"&ОКРУГЛ(1440*(1+6/24);)))/1440;0)
+ЕСЛИОШИБКА(ЧСТРОК(ДВССЫЛ(ОКРУГЛ(1440*G6+1;)&":"&ОКРУГЛ(1440*(H6-F6+I6);)) ДВССЫЛ(ОКРУГЛ(1440*(1+22/24)+1;)&":"&ОКРУГЛ(1440*(1+24/24);)))/1440;0)

[/vba]

К сообщению приложен файл: andron359_04.xlsx (13.3 Kb)

МОИ: Ник, Tip box: 41001663842605

Дата: Среда, 07.11.2012, 12:29 | Сообщение № 7

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 142

Репутация: 0 ±

Замечаний: 40% ±

2010

AlexM, Немного неправильно считает ,ночное время с 2200 - 0600

Дата: Среда, 07.11.2012, 14:46 | Сообщение № 8

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4517

Репутация: 1129 ±

Замечаний: 0% ±

Excel 2003

Прошу прощения ошибочка вышла. исправленная формула

Code

=МАКС(0;6/24-G6)+МИН(2/24;1-G6)+МИН(6/24;I6)+МАКС(0;I6-22/24)+(H6-F6-1)*8/24

К сообщению приложен файл: nt_AlexM1.xls (31.5 Kb)

Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.

Gustav

Дата: Среда, 07.11.2012, 15:05 | Сообщение № 9

Группа: Админы

Ранг: Участник клуба

Сообщений: 2829

Репутация: 1190 ±

Замечаний: ±

начинал с Excel 4.0, видел 2.1

Quote (AlexM)

=МАКС(0;6/24-G6)+МИН(2/24;1-G6)+МИН(6/24;I6)+МАКС(0;I6-22/24)+(H6-F6-1)*8/24

Ай, шайтан! Такая маленькая, даже не верится, что считает. А ведь считает!

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

МОИ: Ник, Tip box: 41001663842605

Дата: Среда, 07.11.2012, 15:42 | Сообщение № 10

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 142

Репутация: 0 ±

Замечаний: 40% ±

2010

AlexM, Всё супер то что и хотелось , СПАСИБО человеческое Вам за ваш ум и помощь другим !

саша12345

Дата: Воскресенье, 25.11.2012, 04:51 | Сообщение № 11

Группа: Пользователи

Ранг: Прохожий

Сообщений: 3

Репутация: 0 ±

Замечаний: 0% ±

всем привет
мне помогите плз тоже прописать формулу для ночных
там есть формула но только она не фурычит

К сообщению приложен файл: _Microsoft_Offi.xlsx (12.7 Kb)

Сообщение отредактировал саша12345 - Воскресенье, 25.11.2012, 05:12

Дата: Воскресенье, 25.11.2012, 11:53 | Сообщение № 12

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4517

Репутация: 1129 ±

Замечаний: 0% ±

Excel 2003

См. приложенный файл.

К сообщению приложен файл: _Microsoft_Offi.xls (31.0 Kb)

Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.

саша12345

Дата: Понедельник, 26.11.2012, 05:26 | Сообщение № 13

Группа: Пользователи

Ранг: Прохожий

Сообщений: 3

Репутация: 0 ±

Замечаний: 0% ±

AlexM, да это норм, а возможно написать формулу для случая когда сотрудник работает с 21 до 9 утра???

К сообщению приложен файл: 3857332.xls (31.0 Kb)

Дата: Понедельник, 26.11.2012, 08:34 | Сообщение № 14

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4517

Репутация: 1129 ±

Замечаний: 0% ±

Excel 2003

Для 4 июл. в вашей таблице введен такой вариант. Если время ухода меньше времени прихода, то считаем уход 5 июля. В формуле ночное время с 22 до 6 утра.
См. приложенный файл.

К сообщению приложен файл: _Microsoft_Offi.xls (35.5 Kb)

Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.

Сообщение отредактировал AlexM - Понедельник, 26.11.2012, 08:37

саша12345

Дата: Понедельник, 26.11.2012, 16:21 | Сообщение № 15

Группа: Пользователи

Ранг: Прохожий

Сообщений: 3

Репутация: 0 ±

Замечаний: 0% ±

AlexM, Спасибо огромное)))

Дата: Понедельник, 26.11.2012, 18:30 | Сообщение № 16

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4517

Репутация: 1129 ±

Замечаний: 0% ±

Excel 2003

Еще вариант. См. файл

К сообщению приложен файл: _Microsoft_Offi.xls (35.5 Kb)

Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.

Shylo

Дата: Вторник, 25.02.2014, 14:21 | Сообщение № 17

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 174

Репутация: 7 ±

Замечаний: 0% ±

Excel-2007; 2016

Добрый день. Прошу помощи. Возникла такая же необходимость подсчета ночных часов. Из приведенного примера попытался самостоятельно пристроить формулу, но не получилось. Закавыка в том, что заступление на работу (явка) туда, допустим 01.01.14г., а заступление на работу (назад), может быть и 02 и 03 и 04.01.14 г. плюс ко всему, эти рабочие файлы используются на планшете под ANDROIDом, в программке (Document To Go) с набором функций, наверно на уровне Excel-97. То есть, не все встроенные функции от 2007, 2013 поддерживаются. Заранее благодарю за помощь.

К сообщению приложен файл: _13.xls (20.5 Kb)

Сообщение отредактировал Shylo - Вторник, 25.02.2014, 14:31