Поиск  Пользователи  Правила 
Закрыть
Логин:
Пароль:
Забыли свой пароль?
Регистрация
Войти
 
Страницы: 1 2 След.
RSS
Разница в минутах между датами без учета определенных условий
 
Здравствуйте.
Помогите, пожалуйста, написать формулу. Имеются две даты в формате ДД.ММ.ГГГГ ЧЧ:ММ:СС, нужно определить разницу между ними в минутах, но в этой разнице не должны учитываться:
1. Праздничные дни
2. Выходные дни
3. Нерабочие часы. Рабочие часы это 09:00-13:00 и 14:00-18:00.

Всё было бы просто, если б не эти нерабочие часы - решилось бы с помощью ЧИСТРАБДНИ, да и дело с концом.

Смотрел несколько тем тут на форуме, но ни одна не решает мою задачу так, как нужно...
https://www.planetaexcel.ru/forum/index.php?PAGE_NAME=read&FID=8&TID=38459
https://www.planetaexcel.ru/forum/index.php?PAGE_NAME=read&FID=8&TID=29992
https://www.planetaexcel.ru/forum/index.php?PAGE_NAME=read&FID=8&TID=2246
https://www.planetaexcel.ru/forum/index.php?PAGE_NAME=read&FID=8&TID=9720
 

=24*60*(IF(NETWORKDAYS(A5;B5;праздники!$A$1:$A$59)-2<0;0;NETWORKDAYS(A5;B5;праздники!$A$1:$A$59)-1- NETWORKDAYS(B5;B5;праздники!$A$1:$A$59))/3+
NETWORKDAYS(A5;A5;праздники!$A$1:$A$59)*(MAX(MIN("13:00";IF(INT(A5)=INT(B5);MOD(B5;1);"24:00"))-MAX(MOD(A5;1);"09:00");0)+MAX(MIN("18:00";IF(INT(A5)=INT(B5);MOD(A5;1);"24:00"))-MAX(MOD(A5;1);"14:00");0))+
IF(INT(A5)=INT(B5);0;NETWORKDAYS(B5;B5;праздники!$A$1:$A$59)*(MAX(MIN("13:00";MOD(B5;1))-"09:00";0)+MAX(MIN("18:00";MOD(B5;1))-"14:00";0))))

UPD

Изменено: БМВ - 13 Авг 2018 17:45:33 (Файл ниже)
 
БМВ, я перевел эту формулу на русский, получилось следующее:

=24*60*(ЧИСТРАБДНИ(A2+1;B2-1;праздники!A1:A59)/3+
ЧИСТРАБДНИ(A2;A2;праздники!A1:A59)*(МАКС("13:00"-МАКС(ОСТАТ(A2;1);"09:00");0)+МАКС("18:00"-МАКС(ОСТАТ(A2;1);"14:00");0))+
ЧИСТРАБДНИ(B2;B2;праздники!B1:B59)*(МАКС(МИН("13:00";ОСТАТ(B2;1))-"09:00";0)+МАКС(МИН("18:00";ОСТАТ(B2;1))-"14:00";0)))

И в результате получилась дата. Я ее перевел в числовой формат, получились цифры. Но ради проверки изменил значение в B2 на такое же, как в А2, только на минуту больше, и в результате получается отрицательное значение.
Что-то не то...
Изменено: andronus - 10 Авг 2018 18:58:34
 
Для простоты я не расчитывал что могут быть в пределах одного дня, если надо то тогда придется мудрить.
Первая часть считает по 8 часов каждый рабочий день исключая крайние
вторая за первый день от старта до 24:00, а третья от 00:00 до финиша в последний день.
К стати если есть уверенность что краевые дни рабочие то можно NETWORKDAYS(A2;A2;праздники!$A$1:$A$59) и вторую , убрать.
Изменено: БМВ - 10 Авг 2018 19:09:53
 
БМВ, в том и дело, что может быть и в пределах дня, конечно.Сейчас если в ячейке В2 изменить дату на "03.08.2018 15:04:29" - в результате получится "-479".
 
Исправил выше. Правее проверка, чтоб было понятно как работает.
Изменено: БМВ - 10 Авг 2018 21:28:43
 
Здравствуйте. У меня получился немного другой результат. Вариант с доп. столбцами, на листе Праздники.
 
БМВ, извините, не мог ответить раньше, т.к. был в отъезде.
Спасибо! Прекрасная формула, но видимо она неправильно считает. Если начальной датой будет 01.08.2018 00:00:00 (среда), а конечной - 02.08.2018 23:59:59 (четверг), то в результате получается 1440 минут. Когда как за эти два дня всего 16 рабочих часов, а это 960 минут.
 
gling, у вас схожие результаты :)
 
Цитата
andronus написал:
Спасибо! Прекрасная формула, но видимо она неправильно считает.
неправильно считающая не может быть прекрасной. Нашел две ошибки, видимо усталость пятничная сказалась. Вечерком подправлю.
 
Цитата
БМВ написал:
неправильно считающая не может быть прекрасной.
Ну зачем же так категорично. Если людям нравится, значит прекрасная ;) .
Как говорил Вини Пых Пух:
У меня правильнописание хромает. Оно хорошее, но почему-то хромает.
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
БМВ, проверил обновленную формулу. Если вбить период 01.08.2018 00:00:00 (среда) – 04.08.2018 23:59:59 (суббота), то результат равен 960. Но в этом периоде 3 рабочих дня, в которых 1440 минут.
 
andronus, Вроде подправил.
 
БМВ, наверное, файл не перевыложили. В вашем посте он еще от 15:59.
 
andronus, важно не время , важен результат. Да работа отнимает время, которое мог бы потратить на форум :-)  
 
БМВ, не совсем понял, о чем речь :) Имеете в виду, что перевыложите позже?
 
я #12  не заметил. учел неучтенное.
 
БМВ, к сожалению, в обновленном файле формула такая же, как и в предыдущей версии...
 
=24*60*(IF(NETWORKDAYS(A5;B5;праздники!$A$1:$A$59)-2<0;0;NETWORKDAYS(A5;B5;праздники!$A$1:$A$59)-1- NETWORKDAYS(B5;B5;праздники!$A$1:$A$59))/3+
NETWORKDAYS(A5;A5;праздники!$A$1:$A$59)*(MAX(MIN("13:00";IF(INT(A5)=INT(B5);MOD(B5;1);"24:00"))-MAX(MOD(A5;1);"09:00");0)+MAX(MIN("18:00";IF(INT(A5)=INT(B5);MOD(A5;1);"24:00"))-MAX(MOD(A5;1);"14:00");0))+
IF(INT(A5)=INT(B5);0;NETWORKDAYS(B5;B5;праздники!$A$1:$A$59)*(MAX(MIN("13:00";MOD(B5;1))-"09:00";0)+MAX(MIN("18:00";MOD(B5;1))-"14:00";0))))
 
БМВ, это же и было в старом файле. В новом и старом файле формулы одинаковые. С этой формулой неправильно считаются минуты: если вбить период 01.08.2018 00:00:00 (среда) – 04.08.2018 23:59:59  (суббота), то результат равен 960. Но в этом периоде 3 рабочих дня, в  которых 1440 минут.
 
ну точно это файл и там и там

Выложил сюда, выше уберу.
 
БМВ, ура!
Спасибо вам огромное!
 
Цитата
andronus написал:
Разница в минутах между датами без учета определенных условий
С названием темы косяк: если БЕЗ учёта, то никаких специальных мер принимать не нужно. А вот если с учётом... ))
 
Юрий М, Юрий, ну если читать что без учета минут попадающих под условие ….  ;)  Вот что косяк творит  :D
 
Не, ну я не могу видеть медведя с "косяком"! Скажите ему кто-нибудь, что наркотики ещё большее зло, чем объединённые ячейки ))
 
Цитата
Юрий М написал:
Скажите ему кто-нибудь
БМВ, Наркотики ещё большее зло, чем объединённые ячейки ))
Изменено: gling - 13 Авг 2018 23:53:52
 
Цитата
БМВ написал:
Выложил сюда, выше уберу.
Здравствуйте. Что-то случилось. Берем ваш файл:
1. Если в "Дата начала" вбить дату в будущем, то разница в минутах всё равно считается. Это мелочь по сравнению со вторым пунктом.
2. Если в "Дата начала" вбить "04.09.2018 15:03:29", а в "Дата окончания" вбить "04.09.2018 15:28:50", то в "Разница в минутах" проставляется дефис. Что странно не только потому, что формула в данном случае не срабатывает, но и потому, что в формуле я не вижу нигде дефиса, заключенного в кавычки. Вижу, что в соседних ячейках справа он проставляется, но в С2 нигде ссылок на эти соседние ячейки.

Посмотрите плиз.
 
в Первом "ЕСЛИ" день минус этот же день равно 1. от него отнимают двойку, в результате получается отрицательное число.
и вот это логическое выражение при отрицательном значении подставляет ноль.
Черточка-дефис, о котором вы говорите, это всего лишь подставленный ноль в другом формате. переведите его в числовой формат и убедитесь в этом.

Говоря иначе, формула не работает в пределах того же дня.
 
Цитата
adike написал:
формула не работает в пределах того же дня
Действиительно. Благодарю. А модифицировать формулу можно? Чтобы и в пределах одного дня считала.
 
Хе-хе, а все еще веселее -
24.04.2018
09.06.2018
29.12.2018
- это рабочие субботы
Скажи мне, кудесник, любимец ба’гов...

Страницы: 1 2 След.
Читают тему (гостей: 2)
Наверх