Function to recognize 1st of month?

  • Thread starter Thread starter terryc
  • Start date Start date
T

terryc

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.
 
=DATEDIF(TODAY(),"12/31/"&YEAR(TODAY()),"m")
will give you the no. of completed months left from today.

You can replace today() with a date or refrence to a date (address of cell
containing a date)
 
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.
 
c181 has the date 9/1/08
=DATEDIF(TODAY(),"5/31/"&YEAR(TODAY()),"m") = 4
=DATEDIF(c181(),"5/31/"&YEAR(c181()),"m") = #REF!
=DATEDIF(c181,"5/31/"&YEAR(c181),"m") = #NUM!

I'm doing something wrong. Also, would you explain how the formula is
evaluating? There wasn't any examples in the Excel help. If ... then...
else...?
 
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.
Click to expand...

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

I just thought of something...

You'd want to limit the date to stop counting after 5/31/2009 since you're
only interested in the count *up to that date*.

So, with that in mind...

Using cells to hold the date boundaries...

A1: =TODAY()
B1: 5/31/2009

=SUMPRODUCT(--(DAY(ROW(INDIRECT(MIN(A1,B1)&":"&B1)))=1))
 
I'm OK with the ending date 5/31 by using the function Sheeloo suggested.
Please look back a few of my posts to the problems I'm having with the
function. It's returning error messages. Thanks
 
I don't think DATEDIF will do what you want.

DATEDIF counts the number of *full* months from a start date to an end date.

If the start date is 1/15/2009 (or today's date) it counts a month as being
from 1/15/2009 to 2/15/2009 to 3/15/209 to 4/15/2009, etc, etc.
 
Back
Top