Страницы: 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.08.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.08.2018 18:58:34
 
Для простоты я не расчитывал что могут быть в пределах одного дня, если надо то тогда придется мудрить.
Первая часть считает по 8 часов каждый рабочий день исключая крайние
вторая за первый день от старта до 24:00, а третья от 00:00 до финиша в последний день.
К стати если есть уверенность что краевые дни рабочие то можно NETWORKDAYS(A2;A2;праздники!$A$1:$A$59) и вторую , убрать.
Изменено: БМВ - 10.08.2018 19:09:53
По вопросам из тем форума, личку не читаю.
 
БМВ, в том и дело, что может быть и в пределах дня, конечно.Сейчас если в ячейке В2 изменить дату на "03.08.2018 15:04:29" - в результате получится "-479".
 
Исправил выше. Правее проверка, чтоб было понятно как работает.
Изменено: БМВ - 10.08.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.08.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 След.
Наверх