Function to recognize 1st of month?
 

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.

Function to recognize 1st of month?
 

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

"terryc" wrote:

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.

Function to recognize 1st of month?
 

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.

Function to recognize 1st of month?
 

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

"Sheeloo" wrote:

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

"terryc" wrote:

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.

Function to recognize 1st of month?
 

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.

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.

Function to recognize 1st of month?
 

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

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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.

Function to recognize 1st of month?
 

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

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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.

Function to recognize 1st of month?
 

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.

--
Biff
Microsoft Excel MVP


"terryc" wrote in message
...
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

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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.

Function to recognize 1st of month?
 

I don't understand your formula but it works and that's what matters. Thanks

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"terryc" wrote in message
...
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

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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.

Function to recognize 1st of month?
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"terryc" wrote in message
...
I don't understand your formula but it works and that's what matters.
Thanks

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"terryc" wrote in message
...
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

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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.