Count paydays left in year

5
votes

I want to count the number of days that

  1. Occur between now and the end of the year (i.e 31 December), and
  2. That fall on either the 15th or last day of the month (30th for April, June, September, November; 31st for January, March, May, July, August, October, December; 28th for February).

Is there a way to accomplish this?

3
excelexcel-formulaworksheet-function
If a month has 31 days do you still want the 30th day of that month included? Also, how about February? – Doug Glancy
can you use VBA or just function? – KMC
Yep, Dec 31st is included. I'd like to use pure Excel functions. – Anna Lam

3 Answers

6
votes

This seems like it might work (though I think it could likely be tidied up and I'm sure there's 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 15th or later and not the last day of the current month

2
votes

A variant which 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

whereas

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

and

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

To change the days required simply change the {15,30} component ie ={1,2,12,15,31} to count all days that fall on the 1st, 2nd, 12th, 15th and 31st etc

This formula will handle leap years

0
votes

This should work on any day of any year in any version of Excel until year 2078 (much longer in Excel 2007 or later).

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

Note I'm checking for day = 1 or 16 on an offset of 1 day (the idea was to get around varying end-of-month DAY() values).

{Array formula... Press Ctrl+Shift+Enter to commit}

[addendum]

If you do not want to include the current day (e.g., say today is 15 August), use this instead:

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

P.s. I tested on all dates from today through leap day 2016 and beyond and it works a treat. All this does is test row numbers treated as a date serial by the DAY() function to see if they are 1 or 16, but the serial is offset by +1 so it's really checking to see if DAY() is [last day of whatever month] or 15. If the result is true, add 1, else add 0.

[more addenda]

Here 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 the current day

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