Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match function won't recognize values unless I type them | Excel Worksheet Functions | |||
My Excel does not recognize countif function | Excel Worksheet Functions | |||
IF Function doesn't recognize TRUE | Excel Worksheet Functions | |||
When using MONTH function on Blank Cell!! Returns Month=Jan! | Excel Discussion (Misc queries) | |||
How do I get the COUNTIF criteria to recognize only month and yea. | Excel Worksheet Functions |