Geek asks and answers

Payout Schedule per year

I want to count the number of days that

  • Occurs between the present and the end of the year (i.e. December 31) and
  • This is a fall on the 15th or last day of the month (April 30, June, September, November, January 31, January, March, May, July, August, October, December, February 28).

Is there any way to do this?

+5
source share
3 answers

It seems like this might work (although I think it could be removed, and I'm sure there is a better way ...)

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

(12 MONTH (TODAY ()) * 2: Two days for each full remaining month

Plus 2 days if before the 15th

or

Plus 1 day if 15 or later rather than the last day of the current month

+6
source

An option that avoids array formulas.

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 ..

+2

Excel 2078 ( Excel 2007 ).

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

. = 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))

Ps I tested all dates from today through leap day 2016 onwards and it works. All of these are test line numbers processed as a date using a function DAY()to see if they are 1 or 16, but the serial number is offset by +1, so it really checks if DAY()[the last day is any month] or 15. If the result is correct, add 1, otherwise add 0.

[more add-ons]

The following are non-array versions that otherwise work the same way:

Includes the current day:

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

Excludes current day

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

More articles:


All Articles