У Ким есть рабочий лист, в который она помещает дату начала (столбец C) и дату окончания (столбец D). Ей нужен способ подсчитать количество окончаний месяца между двумя датами. Например, если дата начала — 01.01.17, а дата окончания — 31.07.17, количество окончаний месяца равно 7. Если, однако, дата окончания — 30.07.17, число месяца концы — 6.

Вы можете придумать любое количество формул для правильного подсчета. Самая простая формула: если вы всегда можете рассчитывать, что дата в столбце C является началом месяца. В этом случае подойдет следующая формула:

=DATEDIF(C1,D1+1,"m")

Если вы не можете рассчитывать на столбец C, содержащий дату, являющуюся началом месяца, но вы можете рассчитывать, что начальная и конечная даты всегда находятся в одном и том же году, тогда подойдет любая из следующих формул:

=IF(MONTH(D1+1)=MONTH(D1),MONTH(D1)-MONTH(C1),MONTH(D1)-MONTH(C1)+1)

=IF(MONTH(D1+1)=MONTH(D1),MONTH(D1)-1,MONTH(D1))-MONTH(C1)+1 =MONTH(EOMONTH(D1,0))-MONTH(EOMONTH(C1,0))+(EOMONTH(D1,0)=D1)

=IF(EOMONTH(D1,0)=D1,MONTH(D1)-MONTH(C1)+1,MONTH(D1)-MONTH(C1))

=IF(D1<EOMONTH(D1,0),MONTH(D1)-1,MONTH(D1))-MONTH(C1)+1 =MONTH(D1)-MONTH(C1)+IF(D1=EOMONTH(D1,0),1,0)

Если годы в начальной и конечной датах могут быть разными, то формулы немного сложнее. Любой из следующих вариантов вычислит правильное количество окончаний месяца в диапазоне дат:

=12*(YEAR(D1)-YEAR(C1))+MONTH(D1)-MONTH(C1)+INT(D1=EOMONTH(D1,0))

=12*(YEAR(D1)-YEAR(C1))+MONTH(D1)-MONTH(C1)+IF(EOMONTH(D1,0)=D1,1,0)

=12YEAR(D1)+MONTH(D1)-12YEAR(C1)-MONTH(C1)+IF(D1=EOMONTH(D1,0),1,0)

=12YEAR(D1)+MONTH(D1)-12YEAR(C1)-MONTH(C1)+(D1=EOMONTH(D1,0))

=12YEAR(D1)+MONTH(D1)-12YEAR(C1)-MONTH(C1)+(DAY(D1+1)=1)

=MONTH(D1)+12YEAR(D1)-MONTH(C1)-12YEAR(C1)+IF(EOMONTH(D1,0)=D1,1,0)

=(MONTH(D1+1)+12YEAR(D1+1))-(MONTH(C1)+12YEAR(C1))

=IF(D1=EOMONTH(D1,0),1,0)+MONTH(D1)-MONTH(C1)+((YEAR(D1)-YEAR(C1))*12)

=IF(EOMONTH(D1,0)=D1,(MONTH(D1)-MONTH(C1)+1)+(YEAR(D1)-YEAR(C1))12,MONTH(D1)-MONTH(C1)+(YEAR(D1)-YEAR(C1))12)

=IF(D1=EOMONTH(D1,0),DATEDIF(EOMONTH(C1,-1),EOMONTH(D1,0),"m"),DATEDIF(EOMONTH(C1,-1),EOMONTH(D1,0),"m")-1)

=IF(D1<C1,"Error",MONTH(D1)-MONTH(C1)+IF(D1MONTH(D1+1))*1)

=IF(D1<C1,"Error",(YEAR(D1)-YEAR(C1))*12+(MONTH(D1)-MONTH(C1))+(EOMONTH(D1,0)=D1))

=SUM(N(FREQUENCY(EOMONTH(ROW(INDIRECT(C1&":"&D1)),0),EOMONTH(ROW(INDIRECT(C1&":"&D1)),0))>0))-(D1<EOMONTH(D1,0))

Некоторые из этих формул немного длинны, поэтому вы должны внимательно следить за тем, чтобы получить их все, если вы решите использовать одну.

Если вы предпочитаете использовать макрос, то вам может быть интересна следующая пользовательская функция:

Function CountMonthEnds(sd As Date, ed As Date) As Integer     Dim ceom As Integer     Dim cmonth As Integer     Dim lmonth As Integer     Dim x As Date

cmonth = 0     ceom = 0     cmonth = Month(sd)

lmonth = cmonth     ' go to end date + 1 to allow for end date being end of month     For x = sd To ed + 1         cmonth = Month(x)

If cmonth <> lmonth Then             ceom = ceom + 1             lmonth = cmonth         End If     Next x     CountMonthEnds = ceom End Function

Макрос просто перебирает все даты между начальной и конечной датами. Он смотрит на месяц для каждой из дат, и если он изменяется, он считает это «концом месяца». Это количество возвращается функцией.

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (572) применим к Microsoft Excel 2007, 2010, 2013 и 2016.