Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 20
Default 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.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,805
Default 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.

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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.



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 20
Default 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.

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 20
Default 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.






  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
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.






  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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.







  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 20
Default 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.








  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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.










  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 20
Default 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.













  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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.













Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Match function won't recognize values unless I type them bdp1978 Excel Worksheet Functions 5 April 30th 23 07:43 PM
My Excel does not recognize countif function [email protected] Excel Worksheet Functions 4 November 27th 06 05:07 PM
IF Function doesn't recognize TRUE AMY Z. Excel Worksheet Functions 6 October 24th 06 11:28 PM
When using MONTH function on Blank Cell!! Returns Month=Jan! mahou Excel Discussion (Misc queries) 6 January 9th 06 03:46 AM
How do I get the COUNTIF criteria to recognize only month and yea. Omega Excel Worksheet Functions 4 November 6th 04 08:22 AM


All times are GMT +1. The time now is 12:09 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"