View Single Post
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
T. Valko T. Valko is offline
external usenet poster
 
Posts: 15,768
Default Function to recognize 1st of month?

To count the 1st of the months from today to 5/31/2009:

=SUMPRODUCT(--(DAY(ROW(INDIRECT(TODAY()&":"&DATE(2009,5,31))))=1 ))

Or, use a cells to hold the date boundaries:

A1: =TODAY()
B1: enrollment end date = 5/31/2009

=SUMPRODUCT(--(DAY(ROW(INDIRECT(A1&":"&B1)))=1))

--
Biff
Microsoft Excel MVP


"terryc" wrote in message
...
In other words, you want to count how many 1st of the months there are
from
today until 5/31/2009? Yes, you are correct.


When I get to 6/1/09 I won't roll it over to a new benefit year. We save
the
area of the worksheet because it crosses fiscal years. We start the
process
over lower on the worksheet. I would need to reset the dates in the
formula.
Thank you.


"T. Valko" wrote:

In other words, you want to count how many 1st of the months there are
from
today until 5/31/2009?

What should happen on 6/1/2010? Should the benefit year automatically
rollover to the next year. In other words, on 6/1/2009 the benefit year
ending date automatically changes to 5/31/2010.

--
Biff
Microsoft Excel MVP


"terryc" wrote in message
...
Employee benefit period is 6/1-5/31. An employee pays 20% of our cost
for
medical insurance. Since there may be 2 or 3 paychecks in a month, we
divide
the annual cost by 26 paychecks per year.

Is there a function that recognizes 6/1, 7/1 etc so I could calculate
how
many months remain in the benefit year* cost of insurance * 20%?
Thanks.