Подсчитайте оставшиеся зарплаты в году

Я хочу посчитать, сколько дней

  1. Происходят с настоящего момента до конца года (т.е. 31 декабря), и
  2. Это выпадает на 15-й или последний день месяца (30-е для апреля, июня, сентября, ноября; 31-е для января, марта, мая, июля, августа, октября, декабря; 28-го для февраля).

Есть ли способ добиться этого?


excel excel-formula worksheet-function
person Anna Lam    schedule 08.08.2012    source источник
comment
Если в месяце 31 день, хотите ли вы включить 30-й день этого месяца? А как насчет февраля?   -  person Doug Glancy    schedule 08.08.2012
comment
вы можете использовать VBA или просто работать?   -  person KMC    schedule 08.08.2012
comment
Да, 31 декабря включено. Я бы хотел использовать чистые функции Excel.   -  person Anna Lam    schedule 08.08.2012

Ответы (3)


arrow_upward
6
arrow_downward

Похоже, это может сработать (хотя я думаю, что это, вероятно, можно было бы привести в порядок, и я уверен, что есть способ получше ...)

=(12-MONTH(TODAY()))*2 
+ IF(DAY(TODAY())<15,2,
        IF(DAY(TODAY())<DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1),1,0))

(12-МЕСЯЦ (СЕГОДНЯ ())) * 2: два дня за каждый полный оставшийся месяц

Плюс 2 дня, если до 15 числа

or

Плюс 1 день, если 15-е или позже, но не в последний день текущего месяца

person Tim Williams    schedule 08.08.2012
comment
+ 1 Приятный на все конец месяца. Возвращает 10 как надо. :) - person Siddharth Rout; 08.08.2012
comment
Это работает как во сне. Я изменил меньше чем на меньше или равно (последняя строка вашего примера кода), чтобы я мог учесть, что текущий день приходится на 15-е. - person Anna Lam; 08.08.2012

arrow_upward
2
arrow_downward

Вариант, исключающий формулы массива.

1. For your question as asked

=SUMPRODUCT(--(DAY(DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW()))+ROW(INDIRECT("1:"&DATE(YEAR(NOW()),12,31)-TODAY()+1)))={15}))+(13-MONTH(TODAY()))
= 10

2. For my initial interpretation which was to count any days corresponding to a certain day of the month

=SUMPRODUCT(--(DAY(DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW()))+ROW(INDIRECT("1:"&DATE(YEAR(NOW()),12,31)-TODAY()+1)))={15,30}))
=10

в то время как

=SUMPRODUCT(--(DAY(DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW()))+ROW(INDIRECT("1:"&DATE(YEAR(NOW()),12,31)-TODAY()+1)))={15,31}))
=8

а также

=SUMPRODUCT(--(DAY(DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW()))+ROW(INDIRECT("1:"&DATE(YEAR(NOW()),12,31)-TODAY()+1)))={15,30,31}))
=13

Чтобы изменить требуемые дни, просто измените компонент {15,30}, т.е. = {1,2,12,15,31}, чтобы подсчитать все дни, приходящиеся на 1, 2, 12, 15 и 31 и т. Д.

Эта формула будет обрабатывать високосные годы

person brettdj    schedule 08.08.2012
comment
+ 1 Хорошее использование SUMPRODUCT. Формула возвращает 8, как и должно быть. - person Siddharth Rout; 08.08.2012
comment
brettdj. Это дает неожиданный результат для {15,30,31}. Он должен вернуть 10 или я что-то упустил? - person Siddharth Rout; 08.08.2012
comment
@SiddharthRout Должно быть 13 - что есть :) (все месяцы с августа по декабрь имеют не менее 30 дней, поэтому 8 (из предыдущего вопроса) +5 = 13) - person brettdj; 08.08.2012
comment
Ха-ха-ха ... Здесь все еще 6:39: D - person Siddharth Rout; 08.08.2012
comment
Что за -- в формуле? - person Enigmativity; 08.08.2012
comment
@Enigmativity corercion для преобразования ИСТИНА в 1 путем умножения на -1*-1. - person brettdj; 08.08.2012
comment
Могут быть некоторые граничные условия, которые не работают. Например. в формуле 1 NOW() as 15 декабря 2012 года возвращает 1 (как если бы 15-е число не учитывалось), но NOW() as 31 декабря 2012 г. также возвращает 1 (как если бы 31-е число должно считаться). Неясно, хочет ли OP включить в результат текущую дату, но не похоже, что результат должен зависеть от того, является ли TODAY() EOM или произвольным днем ​​середины месяца. - person andy holaday; 08.08.2012
comment
@andyholaday Более гибкая формула 2 исключает граничное условие на 31 декабря 2012 г. (т.е. возвращает 0). Я обновлю часть формулы 1, не являющуюся массивом, для единообразия. Вы справедливо заявляете, что нужно было указать текущее условие даты. - person brettdj; 08.08.2012

arrow_upward
0
arrow_downward

Это должно работать в любой день любого года в любой версии Excel до 2078 года (гораздо дольше в Excel 2007 или новее).

=SUM(IF(DAY(ROW(OFFSET($A$1,TODAY(),0,DATE(YEAR(TODAY()),12,31)-TODAY()+1)))={1,16},1,0))

Обратите внимание, что я проверяю day = 1 или 16 со смещением 1 день (идея заключалась в том, чтобы обойти различные значения DAY() в конце месяца).

{Формула массива ... Нажмите Ctrl+Shift+Enter, чтобы зафиксировать}

[приложение]

Если вы не хотите включать текущий день (например, скажем, сегодня 15 августа), используйте это вместо этого:

=SUM(IF(DAY(ROW(OFFSET($A$1,TODAY()+1,0,DATE(YEAR(TODAY()),12,31)-TODAY()+1)))={1,16},1,0))

P.s. Я тестировал все даты с сегодняшнего дня по високосный 2016 и далее, и это работает. Все это проверяет номера строк, обрабатываемые функцией DAY() как серийные даты, чтобы увидеть, равны ли они 1 или 16, но серийный номер смещен на +1, поэтому он действительно проверяет, является ли DAY() [последним днем ​​любого месяца] или 15. Если результат верный, добавьте 1, иначе добавьте 0.

[дополнительные дополнения]

Вот версии без массива, которые в остальном работают одинаково:

Включает текущий день:

=SUMPRODUCT(N(DAY(ROW(OFFSET($A$1,TODAY(),0,DATE(YEAR(TODAY()),12,31)-TODAY()+1)))={1,16}))

Исключает текущий день

=SUMPRODUCT(N(DAY(ROW(OFFSET($A$1,TODAY(),0,DATE(YEAR(TODAY()),12,31)-TODAY()+1)))={1,16}))
person andy holaday    schedule 08.08.2012