Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 27th 09, 11:31 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2008
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  
Old January 27th 09, 11:45 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2008
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  
Old January 28th 09, 12:13 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
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  
Old January 28th 09, 02:59 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2008
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  
Old January 28th 09, 03:03 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2008
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  
Old January 28th 09, 06:53 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
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  
Old January 28th 09, 10:34 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
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  
Old January 30th 09, 06:49 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2008
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  
Old January 30th 09, 08:20 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
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  
Old January 30th 09, 08:59 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2008
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.













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 4 August 28th 07 09:28 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 09:33 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017