Блог

Я знаю, что об этом спрашивали много раз.. но я не могу заставить это работать для моего сценария. Мне нужно рассчитать количество дней занятости между 2 датами, но мне также нужно учитывать год … (т. Е. с января по декабрь 2020 года по декабрь 2021 года.

Моя формула для вычислений такова: =MAX(0; MIN(EOMONTH(C$1; 0); $B2)-MAX(C$1; $A2) 1) где C1 — месяц, который я ищу, B2 — дата окончания, A2 — дата начала…

вот мой скриншот:

Это результаты, которые я ожидаю:

Я думаю, что у меня возникла проблема с форматированием, которую я не знаю, как преодолеть… при большем тестировании похоже, что моя формула была в порядке в первую очередь:

1. Я создаю пустой лист и вручную добавляю даты начала — даты окончания и месяцы столбца заголовка, чтобы попробовать формулу, форматирую ее как дату (с * как дата — * 14.3.20), и все работает нормально… это мой скриншот:

2. Когда я копирую даты начала и окончания вставки с разных листов (у меня уже есть тысячи дат начала и окончания… было бы невозможно вручную ввести их все). Затем я вставляю специальные значения, затем в формате я выбираю дату в том же формате, что и мой тестовый лист Excel … ничего, это просто не сработает… как я могу форматировать даты по вставленным значениям? Вот скриншот, что это неправильно…

Я выбрал столбец даты начала и столбец даты окончания, чем:

Данные> Текст в столбцы Далее, Далее, затем выберите дату «DMY» …. сделал свое дело.

Даты начала и окончания преобразуются обратно в даты, и у меня все начало работать….

Даты, которые были импортированы из базы данных, и перед ними было начальное пространство … вот почему вычисления завершились неудачей. Как только они возвращаются к реальным датам, все начинает работать…

Спасибо за помощь, ребята!

Я просто публикую другой вариант, основанный на IF, который я разработал.

=IF(OR(AND(C$1>$A2,$B2>C$1),AND(EOMONTH(C$1,0)>$A2,$B2>EOMONTH(C$1,0))),MIN($B2,EOMONTH(C$1,0))-MAX($A2,C$1) 1,0)

Основное условие — проверить, пересекается ли какая-либо дата, указанная в строке 1 (в течение месяца), с начальной или конечной датой, указанной в ячейке A2 и B2 . Как только это будет очищено, основная логика, которую вы опубликовали, работает просто отлично.

Вам нужно будет изменить разделители аргументов в соответствии с вашим языком!

Редактировать—

@AnilGoyal обнаружил ошибку формулы, и я публикую пересмотренную формулу. Я ввел эти недостающие AND условия OR . В дополнение к этому в формуле было бы пропущено граничное условие, при котором дата была первым или последним днем месяца.

=IF(OR(AND(C$1>=$A2,C$1<=$B2),AND(EOMONTH(C$1,0)>=$A2,EOMONTH(C$1,0)<=$B2),AND($A2<=EOMONTH(C$1,0),$A2>=C$1),AND($B2<=EOMONTH(C$1,0),$B2>=C$1)),MIN($B2,EOMONTH(C$1,0))-MAX($A2,C$1) 1,0)

В целом, исходная формула OP должна работать хорошо. Как выясняется, приведенная ниже формула, похоже, тоже отлично работает. =MAX(0,MIN($B2,EOMONTH(E$1,0))-MAX($A2,E$1) 1)

ВВЕДИТЕ ЭТУ ФОРМУЛУ В C2

 =IF(AND(C$1<MIN($A2,$B2),MONTH($A2)>MONTH(C$1)),0,IF(C$1>MAX($A2,$B2), 0,IF(C$1>=MIN(C$1,$A2),MIN(EOMONTH(C$1,0),$B2)-MAX($A2,C$1) 1,-MIN($B2,C$1) $A2 1)))