+ Reply to Thread
Results 1 to 194 of 194

Number of paychecks in a month?

  1. 08-19-2005, 12:39 PM #1
    jkh1978
    jkh1978 is offline
    Registered User
    Join Date
    08-19-2005
    Posts
    6

    Question Number of paychecks in a month?

    In my spreadsheet, I have a column for each month, 8/1/05, 9/1/05, etc...

    What I want to know is, how many paychecks will I receive each month?

    I get paid every other week on a Friday.

    My first attempt with help from reading other posts was to do this

    =(4+(DAY(D1-DAY(D1)+1-WEEKDAY(D1-DAY(D1+5))+35)>7))/2

    To get the number of fridays, divided by 2. But that returns 2.5 in some months, and I dont know which of these 4 months each year gets me the extra paycheck.

    Thanks for the help
    Reply With QuoteRegister To Reply
  2. 08-19-2005, 02:05 PM #2
    Bob Phillips
    Guest

    Re: Number of paychecks in a month?

    Nor do we. If you get paid in the final fri of a 4 fri month, you will only
    get 2 the following month even if it has 5 fri.

    It all depends upon where you start from, only you know that.

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > In my spreadsheet, I have a column for each month, 8/1/05, 9/1/05,
    > etc...
    >
    > What I want to know is, how many paychecks will I receive each month?
    >
    > I get paid every other week on a Friday.
    >
    > My first attempt with help from reading other posts was to do this
    >
    > =(4+(DAY(D1-DAY(D1)+1-WEEKDAY(D1-DAY(D1+5))+35)>7))/2
    >
    > To get the number of fridays, divided by 2. But that returns 2.5 in
    > some months, and I dont know which of these 4 months each year gets me
    > the extra paycheck.
    >
    > Thanks for the help
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  3. 08-20-2005, 11:57 AM #3
    jkh1978
    jkh1978 is offline
    Registered User
    Join Date
    08-19-2005
    Posts
    6
    That's part of what I'm stuggling with... how do I get that fact into the formula?

    September is a 3 paycheck month.
    Reply With QuoteRegister To Reply
  4. 08-20-2005, 02:05 PM #4
    RagDyeR
    Guest

    Re: Number of paychecks in a month?

    One way.

    You get paid every 14 days ... right?

    Put the date of your first paycheck in say Z1.
    In Z2 enter
    =Z1+14
    And copy down, giving you a list of paydays.

    Then enter this formula in the appropriate row in say Column A for January,
    and copy across for each month:

    =SUMPRODUCT(--(TEXT($Z$1:$Z$26,"mm-yy")=TEXT(A1,"mm-yy")))

    --

    HTH,

    RD
    =====================================================
    Please keep all correspondence within the Group, so all may benefit!
    =====================================================

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...

    That's part of what I'm stuggling with... how do I get that fact into
    the formula?

    September is a 3 paycheck month.


    --
    jkh1978
    ------------------------------------------------------------------------
    jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    View this thread: http://www.excelforum.com/showthread...hreadid=397278


    Reply With QuoteRegister To Reply
  5. 08-21-2005, 03:08 PM #5
    jkh1978
    jkh1978 is offline
    Registered User
    Join Date
    08-19-2005
    Posts
    6
    Thanks for the reply... but thats not what I need. I need to know the number of paychecks in a month.

    Some months have 5 fridays, so its unclear if its a month that i get 2 or 3 paychecks. What forumula would tell me this?
    Reply With QuoteRegister To Reply
  6. 08-21-2005, 04:09 PM #6
    Cutter
    Cutter is offline
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451
    Not a very elegant solution but it gives you what you want:

    As RagDyer said put your first pay date of the year in Z1
    In Z2 put =Z1+14
    copy down until you get a date past December
    In AA1 put =MONTH(Z1) and format as number (no decimals)
    Copy this down to the same row as your last formula in Col Z

    Now in a cell in the column that has your January date put this formula:
    =COUNTIF(AA1:AA26,COLUMNS($A1:A1))
    copy this formula across the remaining 11 columns holding your dates
    NOTE: I used column A as an example - change it to match the column holding your January dates
    Reply With QuoteRegister To Reply
  7. 08-21-2005, 05:05 PM #7
    Ragdyer
    Guest

    Re: Number of paychecks in a month?

    The formula that I suggested will count the paychecks in each month and
    display that count wherever you insert the formula.
    The formula is set to be copied to each column, and read the column date
    from the top row that column, and compare that date to the list of your
    paydays that I directed you to create, in an "out-of-the-way" location of
    your sheet.

    Would you care to try it before commenting that it doesn't do what you wish?
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------
    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Thanks for the reply... but thats not what I need. I need to know the
    > number of paychecks in a month.
    >
    > Some months have 5 fridays, so its unclear if its a month that i get 2
    > or 3 paychecks. What forumula would tell me this?
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  8. 08-21-2005, 06:05 PM #8
    Bob Phillips
    Guest

    Re: Number of paychecks in a month?

    Well I tried it and it tells me that info.

    You do not to setup the table the start with, but it's fine then.

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Thanks for the reply... but thats not what I need. I need to know the
    > number of paychecks in a month.
    >
    > Some months have 5 fridays, so its unclear if its a month that i get 2
    > or 3 paychecks. What forumula would tell me this?
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  9. 08-21-2005, 06:05 PM #9
    Sandy Mann
    Guest

    Re: Number of paychecks in a month?

    One way is to check is the Friday 28 days ahead of the 1st Friday in the
    month is still in the same month.

    Adapting a formula for the 1st Friday originally posted by Ron Rosenfeld
    almost exactly a year ago try:

    =IF(MONTH(DATE(YEAR(A1),MONTH(A1),1)+7-WEEKDAY(DATE(YEAR(A1),MONTH(A1),2))+28)=MONTH(A1),"3
    paychecks","2 paychecks")

    With any date in the target month in A1
    --
    HTH

    Sandy
    [email protected]
    Replace@mailinator with @tiscali.co.uk


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > In my spreadsheet, I have a column for each month, 8/1/05, 9/1/05,
    > etc...
    >
    > What I want to know is, how many paychecks will I receive each month?
    >
    > I get paid every other week on a Friday.
    >
    > My first attempt with help from reading other posts was to do this
    >
    > =(4+(DAY(D1-DAY(D1)+1-WEEKDAY(D1-DAY(D1+5))+35)>7))/2
    >
    > To get the number of fridays, divided by 2. But that returns 2.5 in
    > some months, and I dont know which of these 4 months each year gets me
    > the extra paycheck.
    >
    > Thanks for the help
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    > http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >



    Reply With QuoteRegister To Reply
  10. 08-22-2005, 06:11 PM #10
    jkh1978
    jkh1978 is offline
    Registered User
    Join Date
    08-19-2005
    Posts
    6
    Sorry RagDyeR , I may have misundertstood what you meant.

    The formula from Sandy Mann is not right... It tells me the months with 3 weeks, not those two months where the extra paycheck falls.
    Reply With QuoteRegister To Reply
  11. 08-22-2005, 09:05 PM #11
    RagDyer
    Guest

    Re: Number of paychecks in a month?

    Did you get it all sorted out, or are you still having a problem?
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sorry RagDyeR , I may have misundertstood what you meant.
    >
    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls.
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  12. 08-22-2005, 09:34 PM #12
    jkh1978
    jkh1978 is offline
    Registered User
    Join Date
    08-19-2005
    Posts
    6
    I still don't have the answer I need.
    Reply With QuoteRegister To Reply
  13. 08-23-2005, 12:05 PM #13
    RagDyeR
    Guest

    Re: Number of paychecks in a month?

    Would you like me to send you my test sheet?

    If so, post back with your address in pseudo text so the spammers can't
    decipher it.
    --

    Regards,

    RD
    ----------------------------------------------------------------------------
    -------------------
    Please keep all correspondence within the Group, so all may benefit !
    ----------------------------------------------------------------------------
    -------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...

    I still don't have the answer I need.


    --
    jkh1978
    ------------------------------------------------------------------------
    jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    View this thread: http://www.excelforum.com/showthread...hreadid=397278


    Reply With QuoteRegister To Reply
  14. 08-23-2005, 01:05 PM #14
    Sandy Mann
    Guest

    Re: Number of paychecks in a month?

    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls

    That is because I failed to check if the first Friday was a pay day. With
    any pay Friday in D1 try:

    =IF((AND(MOD(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+2)-D1,14)=0,MONTH(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+2)+28)=MONTH(A1))),"3
    paychecks","2 paychecks")

    (Shamlessly stealing a formula for the 1st Friday form Daniel.M this time
    <g>)

    --
    HTH

    Sandy
    [email protected]
    Replace@mailinator with @tiscali.co.uk


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sorry RagDyeR , I may have misundertstood what you meant.
    >
    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls.
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    > http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  15. 08-23-2005, 07:25 PM #15
    jkh1978
    jkh1978 is offline
    Registered User
    Join Date
    08-19-2005
    Posts
    6
    Sandy Mann, I tried yours but only got 2 for every month

    Please send a test spreadsheet to jkh1978 AT hotmail.com
    Reply With QuoteRegister To Reply
  16. 08-23-2005, 10:05 PM #16
    RagDyer
    Guest

    Re: Number of paychecks in a month?

    Sample test sheet is on the way.
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sandy Mann, I tried yours but only got 2 for every month
    >
    > Please send a test spreadsheet to jkh1978 AT hotmail.com
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  17. 09-05-2005, 10:05 PM #17
    Bob Phillips
    Guest

    Re: Number of paychecks in a month?

    Nor do we. If you get paid in the final fri of a 4 fri month, you will only
    get 2 the following month even if it has 5 fri.

    It all depends upon where you start from, only you know that.

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > In my spreadsheet, I have a column for each month, 8/1/05, 9/1/05,
    > etc...
    >
    > What I want to know is, how many paychecks will I receive each month?
    >
    > I get paid every other week on a Friday.
    >
    > My first attempt with help from reading other posts was to do this
    >
    > =(4+(DAY(D1-DAY(D1)+1-WEEKDAY(D1-DAY(D1+5))+35)>7))/2
    >
    > To get the number of fridays, divided by 2. But that returns 2.5 in
    > some months, and I dont know which of these 4 months each year gets me
    > the extra paycheck.
    >
    > Thanks for the help
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  18. 09-05-2005, 10:05 PM #18
    RagDyeR
    Guest

    Re: Number of paychecks in a month?

    One way.

    You get paid every 14 days ... right?

    Put the date of your first paycheck in say Z1.
    In Z2 enter
    =Z1+14
    And copy down, giving you a list of paydays.

    Then enter this formula in the appropriate row in say Column A for January,
    and copy across for each month:

    =SUMPRODUCT(--(TEXT($Z$1:$Z$26,"mm-yy")=TEXT(A1,"mm-yy")))

    --

    HTH,

    RD
    =====================================================
    Please keep all correspondence within the Group, so all may benefit!
    =====================================================

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...

    That's part of what I'm stuggling with... how do I get that fact into
    the formula?

    September is a 3 paycheck month.


    --
    jkh1978
    ------------------------------------------------------------------------
    jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    View this thread: http://www.excelforum.com/showthread...hreadid=397278


    Reply With QuoteRegister To Reply
  19. 09-05-2005, 10:05 PM #19
    Ragdyer
    Guest

    Re: Number of paychecks in a month?

    The formula that I suggested will count the paychecks in each month and
    display that count wherever you insert the formula.
    The formula is set to be copied to each column, and read the column date
    from the top row that column, and compare that date to the list of your
    paydays that I directed you to create, in an "out-of-the-way" location of
    your sheet.

    Would you care to try it before commenting that it doesn't do what you wish?
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------
    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Thanks for the reply... but thats not what I need. I need to know the
    > number of paychecks in a month.
    >
    > Some months have 5 fridays, so its unclear if its a month that i get 2
    > or 3 paychecks. What forumula would tell me this?
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  20. 09-05-2005, 10:05 PM #20
    Bob Phillips
    Guest

    Re: Number of paychecks in a month?

    Well I tried it and it tells me that info.

    You do not to setup the table the start with, but it's fine then.

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Thanks for the reply... but thats not what I need. I need to know the
    > number of paychecks in a month.
    >
    > Some months have 5 fridays, so its unclear if its a month that i get 2
    > or 3 paychecks. What forumula would tell me this?
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  21. 09-05-2005, 10:05 PM #21
    Sandy Mann
    Guest

    Re: Number of paychecks in a month?

    One way is to check is the Friday 28 days ahead of the 1st Friday in the
    month is still in the same month.

    Adapting a formula for the 1st Friday originally posted by Ron Rosenfeld
    almost exactly a year ago try:

    =IF(MONTH(DATE(YEAR(A1),MONTH(A1),1)+7-WEEKDAY(DATE(YEAR(A1),MONTH(A1),2))+28)=MONTH(A1),"3
    paychecks","2 paychecks")

    With any date in the target month in A1
    --
    HTH

    Sandy
    [email protected]
    Replace@mailinator with @tiscali.co.uk


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > In my spreadsheet, I have a column for each month, 8/1/05, 9/1/05,
    > etc...
    >
    > What I want to know is, how many paychecks will I receive each month?
    >
    > I get paid every other week on a Friday.
    >
    > My first attempt with help from reading other posts was to do this
    >
    > =(4+(DAY(D1-DAY(D1)+1-WEEKDAY(D1-DAY(D1+5))+35)>7))/2
    >
    > To get the number of fridays, divided by 2. But that returns 2.5 in
    > some months, and I dont know which of these 4 months each year gets me
    > the extra paycheck.
    >
    > Thanks for the help
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    > http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >



    Reply With QuoteRegister To Reply
  22. 09-05-2005, 10:05 PM #22
    RagDyer
    Guest

    Re: Number of paychecks in a month?

    Did you get it all sorted out, or are you still having a problem?
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sorry RagDyeR , I may have misundertstood what you meant.
    >
    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls.
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  23. 09-05-2005, 10:05 PM #23
    RagDyeR
    Guest

    Re: Number of paychecks in a month?

    Would you like me to send you my test sheet?

    If so, post back with your address in pseudo text so the spammers can't
    decipher it.
    --

    Regards,

    RD
    ----------------------------------------------------------------------------
    -------------------
    Please keep all correspondence within the Group, so all may benefit !
    ----------------------------------------------------------------------------
    -------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...

    I still don't have the answer I need.


    --
    jkh1978
    ------------------------------------------------------------------------
    jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    View this thread: http://www.excelforum.com/showthread...hreadid=397278


    Reply With QuoteRegister To Reply
  24. 09-05-2005, 10:05 PM #24
    Sandy Mann
    Guest

    Re: Number of paychecks in a month?

    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls

    That is because I failed to check if the first Friday was a pay day. With
    any pay Friday in D1 try:

    =IF((AND(MOD(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+2)-D1,14)=0,MONTH(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+2)+28)=MONTH(A1))),"3
    paychecks","2 paychecks")

    (Shamlessly stealing a formula for the 1st Friday form Daniel.M this time
    <g>)

    --
    HTH

    Sandy
    [email protected]
    Replace@mailinator with @tiscali.co.uk


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sorry RagDyeR , I may have misundertstood what you meant.
    >
    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls.
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    > http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  25. 09-05-2005, 10:05 PM #25
    RagDyer
    Guest

    Re: Number of paychecks in a month?

    Sample test sheet is on the way.
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sandy Mann, I tried yours but only got 2 for every month
    >
    > Please send a test spreadsheet to jkh1978 AT hotmail.com
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  26. 09-05-2005, 11:05 PM #26
    Bob Phillips
    Guest

    Re: Number of paychecks in a month?

    Nor do we. If you get paid in the final fri of a 4 fri month, you will only
    get 2 the following month even if it has 5 fri.

    It all depends upon where you start from, only you know that.

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > In my spreadsheet, I have a column for each month, 8/1/05, 9/1/05,
    > etc...
    >
    > What I want to know is, how many paychecks will I receive each month?
    >
    > I get paid every other week on a Friday.
    >
    > My first attempt with help from reading other posts was to do this
    >
    > =(4+(DAY(D1-DAY(D1)+1-WEEKDAY(D1-DAY(D1+5))+35)>7))/2
    >
    > To get the number of fridays, divided by 2. But that returns 2.5 in
    > some months, and I dont know which of these 4 months each year gets me
    > the extra paycheck.
    >
    > Thanks for the help
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  27. 09-05-2005, 11:05 PM #27
    RagDyeR
    Guest

    Re: Number of paychecks in a month?

    One way.

    You get paid every 14 days ... right?

    Put the date of your first paycheck in say Z1.
    In Z2 enter
    =Z1+14
    And copy down, giving you a list of paydays.

    Then enter this formula in the appropriate row in say Column A for January,
    and copy across for each month:

    =SUMPRODUCT(--(TEXT($Z$1:$Z$26,"mm-yy")=TEXT(A1,"mm-yy")))

    --

    HTH,

    RD
    =====================================================
    Please keep all correspondence within the Group, so all may benefit!
    =====================================================

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...

    That's part of what I'm stuggling with... how do I get that fact into
    the formula?

    September is a 3 paycheck month.


    --
    jkh1978
    ------------------------------------------------------------------------
    jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    View this thread: http://www.excelforum.com/showthread...hreadid=397278


    Reply With QuoteRegister To Reply
  28. 09-05-2005, 11:05 PM #28
    Ragdyer
    Guest

    Re: Number of paychecks in a month?

    The formula that I suggested will count the paychecks in each month and
    display that count wherever you insert the formula.
    The formula is set to be copied to each column, and read the column date
    from the top row that column, and compare that date to the list of your
    paydays that I directed you to create, in an "out-of-the-way" location of
    your sheet.

    Would you care to try it before commenting that it doesn't do what you wish?
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------
    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Thanks for the reply... but thats not what I need. I need to know the
    > number of paychecks in a month.
    >
    > Some months have 5 fridays, so its unclear if its a month that i get 2
    > or 3 paychecks. What forumula would tell me this?
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  29. 09-05-2005, 11:05 PM #29
    Bob Phillips
    Guest

    Re: Number of paychecks in a month?

    Well I tried it and it tells me that info.

    You do not to setup the table the start with, but it's fine then.

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Thanks for the reply... but thats not what I need. I need to know the
    > number of paychecks in a month.
    >
    > Some months have 5 fridays, so its unclear if its a month that i get 2
    > or 3 paychecks. What forumula would tell me this?
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  30. 09-05-2005, 11:05 PM #30
    Sandy Mann
    Guest

    Re: Number of paychecks in a month?

    One way is to check is the Friday 28 days ahead of the 1st Friday in the
    month is still in the same month.

    Adapting a formula for the 1st Friday originally posted by Ron Rosenfeld
    almost exactly a year ago try:

    =IF(MONTH(DATE(YEAR(A1),MONTH(A1),1)+7-WEEKDAY(DATE(YEAR(A1),MONTH(A1),2))+28)=MONTH(A1),"3
    paychecks","2 paychecks")

    With any date in the target month in A1
    --
    HTH

    Sandy
    [email protected]
    Replace@mailinator with @tiscali.co.uk


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > In my spreadsheet, I have a column for each month, 8/1/05, 9/1/05,
    > etc...
    >
    > What I want to know is, how many paychecks will I receive each month?
    >
    > I get paid every other week on a Friday.
    >
    > My first attempt with help from reading other posts was to do this
    >
    > =(4+(DAY(D1-DAY(D1)+1-WEEKDAY(D1-DAY(D1+5))+35)>7))/2
    >
    > To get the number of fridays, divided by 2. But that returns 2.5 in
    > some months, and I dont know which of these 4 months each year gets me
    > the extra paycheck.
    >
    > Thanks for the help
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    > http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >



    Reply With QuoteRegister To Reply
  31. 09-05-2005, 11:05 PM #31
    RagDyer
    Guest

    Re: Number of paychecks in a month?

    Did you get it all sorted out, or are you still having a problem?
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sorry RagDyeR , I may have misundertstood what you meant.
    >
    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls.
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  32. 09-05-2005, 11:05 PM #32
    RagDyeR
    Guest

    Re: Number of paychecks in a month?

    Would you like me to send you my test sheet?

    If so, post back with your address in pseudo text so the spammers can't
    decipher it.
    --

    Regards,

    RD
    ----------------------------------------------------------------------------
    -------------------
    Please keep all correspondence within the Group, so all may benefit !
    ----------------------------------------------------------------------------
    -------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...

    I still don't have the answer I need.


    --
    jkh1978
    ------------------------------------------------------------------------
    jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    View this thread: http://www.excelforum.com/showthread...hreadid=397278


    Reply With QuoteRegister To Reply
  33. 09-05-2005, 11:05 PM #33
    Sandy Mann
    Guest

    Re: Number of paychecks in a month?

    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls

    That is because I failed to check if the first Friday was a pay day. With
    any pay Friday in D1 try:

    =IF((AND(MOD(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+2)-D1,14)=0,MONTH(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+2)+28)=MONTH(A1))),"3
    paychecks","2 paychecks")

    (Shamlessly stealing a formula for the 1st Friday form Daniel.M this time
    <g>)

    --
    HTH

    Sandy
    [email protected]
    Replace@mailinator with @tiscali.co.uk


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sorry RagDyeR , I may have misundertstood what you meant.
    >
    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls.
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    > http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  34. 09-05-2005, 11:05 PM #34
    RagDyer
    Guest

    Re: Number of paychecks in a month?

    Sample test sheet is on the way.
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sandy Mann, I tried yours but only got 2 for every month
    >
    > Please send a test spreadsheet to jkh1978 AT hotmail.com
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  35. 09-06-2005, 12:05 AM #35
    Bob Phillips
    Guest

    Re: Number of paychecks in a month?

    Nor do we. If you get paid in the final fri of a 4 fri month, you will only
    get 2 the following month even if it has 5 fri.

    It all depends upon where you start from, only you know that.

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > In my spreadsheet, I have a column for each month, 8/1/05, 9/1/05,
    > etc...
    >
    > What I want to know is, how many paychecks will I receive each month?
    >
    > I get paid every other week on a Friday.
    >
    > My first attempt with help from reading other posts was to do this
    >
    > =(4+(DAY(D1-DAY(D1)+1-WEEKDAY(D1-DAY(D1+5))+35)>7))/2
    >
    > To get the number of fridays, divided by 2. But that returns 2.5 in
    > some months, and I dont know which of these 4 months each year gets me
    > the extra paycheck.
    >
    > Thanks for the help
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  36. 09-06-2005, 12:05 AM #36
    RagDyeR
    Guest

    Re: Number of paychecks in a month?

    One way.

    You get paid every 14 days ... right?

    Put the date of your first paycheck in say Z1.
    In Z2 enter
    =Z1+14
    And copy down, giving you a list of paydays.

    Then enter this formula in the appropriate row in say Column A for January,
    and copy across for each month:

    =SUMPRODUCT(--(TEXT($Z$1:$Z$26,"mm-yy")=TEXT(A1,"mm-yy")))

    --

    HTH,

    RD
    =====================================================
    Please keep all correspondence within the Group, so all may benefit!
    =====================================================

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...

    That's part of what I'm stuggling with... how do I get that fact into
    the formula?

    September is a 3 paycheck month.


    --
    jkh1978
    ------------------------------------------------------------------------
    jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    View this thread: http://www.excelforum.com/showthread...hreadid=397278


    Reply With QuoteRegister To Reply
  37. 09-06-2005, 12:05 AM #37
    Ragdyer
    Guest

    Re: Number of paychecks in a month?

    The formula that I suggested will count the paychecks in each month and
    display that count wherever you insert the formula.
    The formula is set to be copied to each column, and read the column date
    from the top row that column, and compare that date to the list of your
    paydays that I directed you to create, in an "out-of-the-way" location of
    your sheet.

    Would you care to try it before commenting that it doesn't do what you wish?
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------
    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Thanks for the reply... but thats not what I need. I need to know the
    > number of paychecks in a month.
    >
    > Some months have 5 fridays, so its unclear if its a month that i get 2
    > or 3 paychecks. What forumula would tell me this?
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  38. 09-06-2005, 12:05 AM #38
    Bob Phillips
    Guest

    Re: Number of paychecks in a month?

    Well I tried it and it tells me that info.

    You do not to setup the table the start with, but it's fine then.

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Thanks for the reply... but thats not what I need. I need to know the
    > number of paychecks in a month.
    >
    > Some months have 5 fridays, so its unclear if its a month that i get 2
    > or 3 paychecks. What forumula would tell me this?
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  39. 09-06-2005, 12:05 AM #39
    Sandy Mann
    Guest

    Re: Number of paychecks in a month?

    One way is to check is the Friday 28 days ahead of the 1st Friday in the
    month is still in the same month.

    Adapting a formula for the 1st Friday originally posted by Ron Rosenfeld
    almost exactly a year ago try:

    =IF(MONTH(DATE(YEAR(A1),MONTH(A1),1)+7-WEEKDAY(DATE(YEAR(A1),MONTH(A1),2))+28)=MONTH(A1),"3
    paychecks","2 paychecks")

    With any date in the target month in A1
    --
    HTH

    Sandy
    [email protected]
    Replace@mailinator with @tiscali.co.uk


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > In my spreadsheet, I have a column for each month, 8/1/05, 9/1/05,
    > etc...
    >
    > What I want to know is, how many paychecks will I receive each month?
    >
    > I get paid every other week on a Friday.
    >
    > My first attempt with help from reading other posts was to do this
    >
    > =(4+(DAY(D1-DAY(D1)+1-WEEKDAY(D1-DAY(D1+5))+35)>7))/2
    >
    > To get the number of fridays, divided by 2. But that returns 2.5 in
    > some months, and I dont know which of these 4 months each year gets me
    > the extra paycheck.
    >
    > Thanks for the help
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    > http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >



    Reply With QuoteRegister To Reply
  40. 09-06-2005, 12:05 AM #40
    RagDyer
    Guest

    Re: Number of paychecks in a month?

    Did you get it all sorted out, or are you still having a problem?
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sorry RagDyeR , I may have misundertstood what you meant.
    >
    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls.
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  41. 09-06-2005, 12:05 AM #41
    RagDyeR
    Guest

    Re: Number of paychecks in a month?

    Would you like me to send you my test sheet?

    If so, post back with your address in pseudo text so the spammers can't
    decipher it.
    --

    Regards,

    RD
    ----------------------------------------------------------------------------
    -------------------
    Please keep all correspondence within the Group, so all may benefit !
    ----------------------------------------------------------------------------
    -------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...

    I still don't have the answer I need.


    --
    jkh1978
    ------------------------------------------------------------------------
    jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    View this thread: http://www.excelforum.com/showthread...hreadid=397278


    Reply With QuoteRegister To Reply
  42. 09-06-2005, 12:05 AM #42
    Sandy Mann
    Guest

    Re: Number of paychecks in a month?

    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls

    That is because I failed to check if the first Friday was a pay day. With
    any pay Friday in D1 try:

    =IF((AND(MOD(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+2)-D1,14)=0,MONTH(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+2)+28)=MONTH(A1))),"3
    paychecks","2 paychecks")

    (Shamlessly stealing a formula for the 1st Friday form Daniel.M this time
    <g>)

    --
    HTH

    Sandy
    [email protected]
    Replace@mailinator with @tiscali.co.uk


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sorry RagDyeR , I may have misundertstood what you meant.
    >
    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls.
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    > http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  43. 09-06-2005, 12:05 AM #43
    RagDyer
    Guest

    Re: Number of paychecks in a month?

    Sample test sheet is on the way.
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sandy Mann, I tried yours but only got 2 for every month
    >
    > Please send a test spreadsheet to jkh1978 AT hotmail.com
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  44. 09-06-2005, 01:05 AM #44
    Bob Phillips
    Guest

    Re: Number of paychecks in a month?

    Nor do we. If you get paid in the final fri of a 4 fri month, you will only
    get 2 the following month even if it has 5 fri.

    It all depends upon where you start from, only you know that.

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > In my spreadsheet, I have a column for each month, 8/1/05, 9/1/05,
    > etc...
    >
    > What I want to know is, how many paychecks will I receive each month?
    >
    > I get paid every other week on a Friday.
    >
    > My first attempt with help from reading other posts was to do this
    >
    > =(4+(DAY(D1-DAY(D1)+1-WEEKDAY(D1-DAY(D1+5))+35)>7))/2
    >
    > To get the number of fridays, divided by 2. But that returns 2.5 in
    > some months, and I dont know which of these 4 months each year gets me
    > the extra paycheck.
    >
    > Thanks for the help
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  45. 09-06-2005, 01:05 AM #45
    RagDyeR
    Guest

    Re: Number of paychecks in a month?

    One way.

    You get paid every 14 days ... right?

    Put the date of your first paycheck in say Z1.
    In Z2 enter
    =Z1+14
    And copy down, giving you a list of paydays.

    Then enter this formula in the appropriate row in say Column A for January,
    and copy across for each month:

    =SUMPRODUCT(--(TEXT($Z$1:$Z$26,"mm-yy")=TEXT(A1,"mm-yy")))

    --

    HTH,

    RD
    =====================================================
    Please keep all correspondence within the Group, so all may benefit!
    =====================================================

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...

    That's part of what I'm stuggling with... how do I get that fact into
    the formula?

    September is a 3 paycheck month.


    --
    jkh1978
    ------------------------------------------------------------------------
    jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    View this thread: http://www.excelforum.com/showthread...hreadid=397278


    Reply With QuoteRegister To Reply
  46. 09-06-2005, 01:05 AM #46
    Ragdyer
    Guest

    Re: Number of paychecks in a month?

    The formula that I suggested will count the paychecks in each month and
    display that count wherever you insert the formula.
    The formula is set to be copied to each column, and read the column date
    from the top row that column, and compare that date to the list of your
    paydays that I directed you to create, in an "out-of-the-way" location of
    your sheet.

    Would you care to try it before commenting that it doesn't do what you wish?
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------
    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Thanks for the reply... but thats not what I need. I need to know the
    > number of paychecks in a month.
    >
    > Some months have 5 fridays, so its unclear if its a month that i get 2
    > or 3 paychecks. What forumula would tell me this?
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  47. 09-06-2005, 01:05 AM #47
    Bob Phillips
    Guest

    Re: Number of paychecks in a month?

    Well I tried it and it tells me that info.

    You do not to setup the table the start with, but it's fine then.

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Thanks for the reply... but thats not what I need. I need to know the
    > number of paychecks in a month.
    >
    > Some months have 5 fridays, so its unclear if its a month that i get 2
    > or 3 paychecks. What forumula would tell me this?
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  48. 09-06-2005, 01:05 AM #48
    Sandy Mann
    Guest

    Re: Number of paychecks in a month?

    One way is to check is the Friday 28 days ahead of the 1st Friday in the
    month is still in the same month.

    Adapting a formula for the 1st Friday originally posted by Ron Rosenfeld
    almost exactly a year ago try:

    =IF(MONTH(DATE(YEAR(A1),MONTH(A1),1)+7-WEEKDAY(DATE(YEAR(A1),MONTH(A1),2))+28)=MONTH(A1),"3
    paychecks","2 paychecks")

    With any date in the target month in A1
    --
    HTH

    Sandy
    [email protected]
    Replace@mailinator with @tiscali.co.uk


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > In my spreadsheet, I have a column for each month, 8/1/05, 9/1/05,
    > etc...
    >
    > What I want to know is, how many paychecks will I receive each month?
    >
    > I get paid every other week on a Friday.
    >
    > My first attempt with help from reading other posts was to do this
    >
    > =(4+(DAY(D1-DAY(D1)+1-WEEKDAY(D1-DAY(D1+5))+35)>7))/2
    >
    > To get the number of fridays, divided by 2. But that returns 2.5 in
    > some months, and I dont know which of these 4 months each year gets me
    > the extra paycheck.
    >
    > Thanks for the help
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    > http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >



    Reply With QuoteRegister To Reply
  49. 09-06-2005, 01:05 AM #49
    RagDyer
    Guest

    Re: Number of paychecks in a month?

    Did you get it all sorted out, or are you still having a problem?
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sorry RagDyeR , I may have misundertstood what you meant.
    >
    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls.
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  50. 09-06-2005, 01:05 AM #50
    RagDyeR
    Guest

    Re: Number of paychecks in a month?

    Would you like me to send you my test sheet?

    If so, post back with your address in pseudo text so the spammers can't
    decipher it.
    --

    Regards,

    RD
    ----------------------------------------------------------------------------
    -------------------
    Please keep all correspondence within the Group, so all may benefit !
    ----------------------------------------------------------------------------
    -------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...

    I still don't have the answer I need.


    --
    jkh1978
    ------------------------------------------------------------------------
    jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    View this thread: http://www.excelforum.com/showthread...hreadid=397278


    Reply With QuoteRegister To Reply
  51. 09-06-2005, 01:05 AM #51
    Sandy Mann
    Guest

    Re: Number of paychecks in a month?

    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls

    That is because I failed to check if the first Friday was a pay day. With
    any pay Friday in D1 try:

    =IF((AND(MOD(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+2)-D1,14)=0,MONTH(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+2)+28)=MONTH(A1))),"3
    paychecks","2 paychecks")

    (Shamlessly stealing a formula for the 1st Friday form Daniel.M this time
    <g>)

    --
    HTH

    Sandy
    [email protected]
    Replace@mailinator with @tiscali.co.uk


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sorry RagDyeR , I may have misundertstood what you meant.
    >
    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls.
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    > http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  52. 09-06-2005, 01:05 AM #52
    RagDyer
    Guest

    Re: Number of paychecks in a month?

    Sample test sheet is on the way.
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sandy Mann, I tried yours but only got 2 for every month
    >
    > Please send a test spreadsheet to jkh1978 AT hotmail.com
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  53. 09-06-2005, 02:05 AM #53
    Bob Phillips
    Guest

    Re: Number of paychecks in a month?

    Nor do we. If you get paid in the final fri of a 4 fri month, you will only
    get 2 the following month even if it has 5 fri.

    It all depends upon where you start from, only you know that.

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > In my spreadsheet, I have a column for each month, 8/1/05, 9/1/05,
    > etc...
    >
    > What I want to know is, how many paychecks will I receive each month?
    >
    > I get paid every other week on a Friday.
    >
    > My first attempt with help from reading other posts was to do this
    >
    > =(4+(DAY(D1-DAY(D1)+1-WEEKDAY(D1-DAY(D1+5))+35)>7))/2
    >
    > To get the number of fridays, divided by 2. But that returns 2.5 in
    > some months, and I dont know which of these 4 months each year gets me
    > the extra paycheck.
    >
    > Thanks for the help
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  54. 09-06-2005, 02:05 AM #54
    RagDyeR
    Guest

    Re: Number of paychecks in a month?

    One way.

    You get paid every 14 days ... right?

    Put the date of your first paycheck in say Z1.
    In Z2 enter
    =Z1+14
    And copy down, giving you a list of paydays.

    Then enter this formula in the appropriate row in say Column A for January,
    and copy across for each month:

    =SUMPRODUCT(--(TEXT($Z$1:$Z$26,"mm-yy")=TEXT(A1,"mm-yy")))

    --

    HTH,

    RD
    =====================================================
    Please keep all correspondence within the Group, so all may benefit!
    =====================================================

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...

    That's part of what I'm stuggling with... how do I get that fact into
    the formula?

    September is a 3 paycheck month.


    --
    jkh1978
    ------------------------------------------------------------------------
    jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    View this thread: http://www.excelforum.com/showthread...hreadid=397278


    Reply With QuoteRegister To Reply
  55. 09-06-2005, 02:05 AM #55
    Ragdyer
    Guest

    Re: Number of paychecks in a month?

    The formula that I suggested will count the paychecks in each month and
    display that count wherever you insert the formula.
    The formula is set to be copied to each column, and read the column date
    from the top row that column, and compare that date to the list of your
    paydays that I directed you to create, in an "out-of-the-way" location of
    your sheet.

    Would you care to try it before commenting that it doesn't do what you wish?
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------
    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Thanks for the reply... but thats not what I need. I need to know the
    > number of paychecks in a month.
    >
    > Some months have 5 fridays, so its unclear if its a month that i get 2
    > or 3 paychecks. What forumula would tell me this?
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  56. 09-06-2005, 02:05 AM #56
    Bob Phillips
    Guest

    Re: Number of paychecks in a month?

    Well I tried it and it tells me that info.

    You do not to setup the table the start with, but it's fine then.

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Thanks for the reply... but thats not what I need. I need to know the
    > number of paychecks in a month.
    >
    > Some months have 5 fridays, so its unclear if its a month that i get 2
    > or 3 paychecks. What forumula would tell me this?
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  57. 09-06-2005, 02:05 AM #57
    Sandy Mann
    Guest

    Re: Number of paychecks in a month?

    One way is to check is the Friday 28 days ahead of the 1st Friday in the
    month is still in the same month.

    Adapting a formula for the 1st Friday originally posted by Ron Rosenfeld
    almost exactly a year ago try:

    =IF(MONTH(DATE(YEAR(A1),MONTH(A1),1)+7-WEEKDAY(DATE(YEAR(A1),MONTH(A1),2))+28)=MONTH(A1),"3
    paychecks","2 paychecks")

    With any date in the target month in A1
    --
    HTH

    Sandy
    [email protected]
    Replace@mailinator with @tiscali.co.uk


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > In my spreadsheet, I have a column for each month, 8/1/05, 9/1/05,
    > etc...
    >
    > What I want to know is, how many paychecks will I receive each month?
    >
    > I get paid every other week on a Friday.
    >
    > My first attempt with help from reading other posts was to do this
    >
    > =(4+(DAY(D1-DAY(D1)+1-WEEKDAY(D1-DAY(D1+5))+35)>7))/2
    >
    > To get the number of fridays, divided by 2. But that returns 2.5 in
    > some months, and I dont know which of these 4 months each year gets me
    > the extra paycheck.
    >
    > Thanks for the help
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    > http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >



    Reply With QuoteRegister To Reply
  58. 09-06-2005, 02:05 AM #58
    RagDyer
    Guest

    Re: Number of paychecks in a month?

    Did you get it all sorted out, or are you still having a problem?
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sorry RagDyeR , I may have misundertstood what you meant.
    >
    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls.
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  59. 09-06-2005, 02:05 AM #59
    RagDyeR
    Guest

    Re: Number of paychecks in a month?

    Would you like me to send you my test sheet?

    If so, post back with your address in pseudo text so the spammers can't
    decipher it.
    --

    Regards,

    RD
    ----------------------------------------------------------------------------
    -------------------
    Please keep all correspondence within the Group, so all may benefit !
    ----------------------------------------------------------------------------
    -------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...

    I still don't have the answer I need.


    --
    jkh1978
    ------------------------------------------------------------------------
    jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    View this thread: http://www.excelforum.com/showthread...hreadid=397278


    Reply With QuoteRegister To Reply
  60. 09-06-2005, 02:05 AM #60
    Sandy Mann
    Guest

    Re: Number of paychecks in a month?

    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls

    That is because I failed to check if the first Friday was a pay day. With
    any pay Friday in D1 try:

    =IF((AND(MOD(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+2)-D1,14)=0,MONTH(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+2)+28)=MONTH(A1))),"3
    paychecks","2 paychecks")

    (Shamlessly stealing a formula for the 1st Friday form Daniel.M this time
    <g>)

    --
    HTH

    Sandy
    [email protected]
    Replace@mailinator with @tiscali.co.uk


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sorry RagDyeR , I may have misundertstood what you meant.
    >
    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls.
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    > http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  61. 09-06-2005, 02:05 AM #61
    RagDyer
    Guest

    Re: Number of paychecks in a month?

    Sample test sheet is on the way.
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sandy Mann, I tried yours but only got 2 for every month
    >
    > Please send a test spreadsheet to jkh1978 AT hotmail.com
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  62. 09-06-2005, 03:05 AM #62
    Bob Phillips
    Guest

    Re: Number of paychecks in a month?

    Nor do we. If you get paid in the final fri of a 4 fri month, you will only
    get 2 the following month even if it has 5 fri.

    It all depends upon where you start from, only you know that.

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > In my spreadsheet, I have a column for each month, 8/1/05, 9/1/05,
    > etc...
    >
    > What I want to know is, how many paychecks will I receive each month?
    >
    > I get paid every other week on a Friday.
    >
    > My first attempt with help from reading other posts was to do this
    >
    > =(4+(DAY(D1-DAY(D1)+1-WEEKDAY(D1-DAY(D1+5))+35)>7))/2
    >
    > To get the number of fridays, divided by 2. But that returns 2.5 in
    > some months, and I dont know which of these 4 months each year gets me
    > the extra paycheck.
    >
    > Thanks for the help
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  63. 09-06-2005, 03:05 AM #63
    RagDyeR
    Guest

    Re: Number of paychecks in a month?

    One way.

    You get paid every 14 days ... right?

    Put the date of your first paycheck in say Z1.
    In Z2 enter
    =Z1+14
    And copy down, giving you a list of paydays.

    Then enter this formula in the appropriate row in say Column A for January,
    and copy across for each month:

    =SUMPRODUCT(--(TEXT($Z$1:$Z$26,"mm-yy")=TEXT(A1,"mm-yy")))

    --

    HTH,

    RD
    =====================================================
    Please keep all correspondence within the Group, so all may benefit!
    =====================================================

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...

    That's part of what I'm stuggling with... how do I get that fact into
    the formula?

    September is a 3 paycheck month.


    --
    jkh1978
    ------------------------------------------------------------------------
    jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    View this thread: http://www.excelforum.com/showthread...hreadid=397278


    Reply With QuoteRegister To Reply
  64. 09-06-2005, 03:05 AM #64
    Ragdyer
    Guest

    Re: Number of paychecks in a month?

    The formula that I suggested will count the paychecks in each month and
    display that count wherever you insert the formula.
    The formula is set to be copied to each column, and read the column date
    from the top row that column, and compare that date to the list of your
    paydays that I directed you to create, in an "out-of-the-way" location of
    your sheet.

    Would you care to try it before commenting that it doesn't do what you wish?
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------
    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Thanks for the reply... but thats not what I need. I need to know the
    > number of paychecks in a month.
    >
    > Some months have 5 fridays, so its unclear if its a month that i get 2
    > or 3 paychecks. What forumula would tell me this?
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  65. 09-06-2005, 03:05 AM #65
    Bob Phillips
    Guest

    Re: Number of paychecks in a month?

    Well I tried it and it tells me that info.

    You do not to setup the table the start with, but it's fine then.

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Thanks for the reply... but thats not what I need. I need to know the
    > number of paychecks in a month.
    >
    > Some months have 5 fridays, so its unclear if its a month that i get 2
    > or 3 paychecks. What forumula would tell me this?
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  66. 09-06-2005, 03:05 AM #66
    Sandy Mann
    Guest

    Re: Number of paychecks in a month?

    One way is to check is the Friday 28 days ahead of the 1st Friday in the
    month is still in the same month.

    Adapting a formula for the 1st Friday originally posted by Ron Rosenfeld
    almost exactly a year ago try:

    =IF(MONTH(DATE(YEAR(A1),MONTH(A1),1)+7-WEEKDAY(DATE(YEAR(A1),MONTH(A1),2))+28)=MONTH(A1),"3
    paychecks","2 paychecks")

    With any date in the target month in A1
    --
    HTH

    Sandy
    [email protected]
    Replace@mailinator with @tiscali.co.uk


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > In my spreadsheet, I have a column for each month, 8/1/05, 9/1/05,
    > etc...
    >
    > What I want to know is, how many paychecks will I receive each month?
    >
    > I get paid every other week on a Friday.
    >
    > My first attempt with help from reading other posts was to do this
    >
    > =(4+(DAY(D1-DAY(D1)+1-WEEKDAY(D1-DAY(D1+5))+35)>7))/2
    >
    > To get the number of fridays, divided by 2. But that returns 2.5 in
    > some months, and I dont know which of these 4 months each year gets me
    > the extra paycheck.
    >
    > Thanks for the help
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    > http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >



    Reply With QuoteRegister To Reply
  67. 09-06-2005, 03:05 AM #67
    RagDyer
    Guest

    Re: Number of paychecks in a month?

    Did you get it all sorted out, or are you still having a problem?
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sorry RagDyeR , I may have misundertstood what you meant.
    >
    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls.
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  68. 09-06-2005, 03:05 AM #68
    RagDyeR
    Guest

    Re: Number of paychecks in a month?

    Would you like me to send you my test sheet?

    If so, post back with your address in pseudo text so the spammers can't
    decipher it.
    --

    Regards,

    RD
    ----------------------------------------------------------------------------
    -------------------
    Please keep all correspondence within the Group, so all may benefit !
    ----------------------------------------------------------------------------
    -------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...

    I still don't have the answer I need.


    --
    jkh1978
    ------------------------------------------------------------------------
    jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    View this thread: http://www.excelforum.com/showthread...hreadid=397278


    Reply With QuoteRegister To Reply
  69. 09-06-2005, 03:05 AM #69
    Sandy Mann
    Guest

    Re: Number of paychecks in a month?

    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls

    That is because I failed to check if the first Friday was a pay day. With
    any pay Friday in D1 try:

    =IF((AND(MOD(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+2)-D1,14)=0,MONTH(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+2)+28)=MONTH(A1))),"3
    paychecks","2 paychecks")

    (Shamlessly stealing a formula for the 1st Friday form Daniel.M this time
    <g>)

    --
    HTH

    Sandy
    [email protected]
    Replace@mailinator with @tiscali.co.uk


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sorry RagDyeR , I may have misundertstood what you meant.
    >
    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls.
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    > http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  70. 09-06-2005, 03:05 AM #70
    RagDyer
    Guest

    Re: Number of paychecks in a month?

    Sample test sheet is on the way.
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sandy Mann, I tried yours but only got 2 for every month
    >
    > Please send a test spreadsheet to jkh1978 AT hotmail.com
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  71. 09-06-2005, 04:05 AM #71
    Bob Phillips
    Guest

    Re: Number of paychecks in a month?

    Nor do we. If you get paid in the final fri of a 4 fri month, you will only
    get 2 the following month even if it has 5 fri.

    It all depends upon where you start from, only you know that.

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > In my spreadsheet, I have a column for each month, 8/1/05, 9/1/05,
    > etc...
    >
    > What I want to know is, how many paychecks will I receive each month?
    >
    > I get paid every other week on a Friday.
    >
    > My first attempt with help from reading other posts was to do this
    >
    > =(4+(DAY(D1-DAY(D1)+1-WEEKDAY(D1-DAY(D1+5))+35)>7))/2
    >
    > To get the number of fridays, divided by 2. But that returns 2.5 in
    > some months, and I dont know which of these 4 months each year gets me
    > the extra paycheck.
    >
    > Thanks for the help
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  72. 09-06-2005, 04:05 AM #72
    RagDyeR
    Guest

    Re: Number of paychecks in a month?

    One way.

    You get paid every 14 days ... right?

    Put the date of your first paycheck in say Z1.
    In Z2 enter
    =Z1+14
    And copy down, giving you a list of paydays.

    Then enter this formula in the appropriate row in say Column A for January,
    and copy across for each month:

    =SUMPRODUCT(--(TEXT($Z$1:$Z$26,"mm-yy")=TEXT(A1,"mm-yy")))

    --

    HTH,

    RD
    =====================================================
    Please keep all correspondence within the Group, so all may benefit!
    =====================================================

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...

    That's part of what I'm stuggling with... how do I get that fact into
    the formula?

    September is a 3 paycheck month.


    --
    jkh1978
    ------------------------------------------------------------------------
    jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    View this thread: http://www.excelforum.com/showthread...hreadid=397278


    Reply With QuoteRegister To Reply
  73. 09-06-2005, 04:05 AM #73
    Ragdyer
    Guest

    Re: Number of paychecks in a month?

    The formula that I suggested will count the paychecks in each month and
    display that count wherever you insert the formula.
    The formula is set to be copied to each column, and read the column date
    from the top row that column, and compare that date to the list of your
    paydays that I directed you to create, in an "out-of-the-way" location of
    your sheet.

    Would you care to try it before commenting that it doesn't do what you wish?
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------
    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Thanks for the reply... but thats not what I need. I need to know the
    > number of paychecks in a month.
    >
    > Some months have 5 fridays, so its unclear if its a month that i get 2
    > or 3 paychecks. What forumula would tell me this?
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  74. 09-06-2005, 04:05 AM #74
    Bob Phillips
    Guest

    Re: Number of paychecks in a month?

    Well I tried it and it tells me that info.

    You do not to setup the table the start with, but it's fine then.

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Thanks for the reply... but thats not what I need. I need to know the
    > number of paychecks in a month.
    >
    > Some months have 5 fridays, so its unclear if its a month that i get 2
    > or 3 paychecks. What forumula would tell me this?
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  75. 09-06-2005, 04:05 AM #75
    Sandy Mann
    Guest

    Re: Number of paychecks in a month?

    One way is to check is the Friday 28 days ahead of the 1st Friday in the
    month is still in the same month.

    Adapting a formula for the 1st Friday originally posted by Ron Rosenfeld
    almost exactly a year ago try:

    =IF(MONTH(DATE(YEAR(A1),MONTH(A1),1)+7-WEEKDAY(DATE(YEAR(A1),MONTH(A1),2))+28)=MONTH(A1),"3
    paychecks","2 paychecks")

    With any date in the target month in A1
    --
    HTH

    Sandy
    [email protected]
    Replace@mailinator with @tiscali.co.uk


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > In my spreadsheet, I have a column for each month, 8/1/05, 9/1/05,
    > etc...
    >
    > What I want to know is, how many paychecks will I receive each month?
    >
    > I get paid every other week on a Friday.
    >
    > My first attempt with help from reading other posts was to do this
    >
    > =(4+(DAY(D1-DAY(D1)+1-WEEKDAY(D1-DAY(D1+5))+35)>7))/2
    >
    > To get the number of fridays, divided by 2. But that returns 2.5 in
    > some months, and I dont know which of these 4 months each year gets me
    > the extra paycheck.
    >
    > Thanks for the help
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    > http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >



    Reply With QuoteRegister To Reply
  76. 09-06-2005, 04:05 AM #76
    RagDyer
    Guest

    Re: Number of paychecks in a month?

    Did you get it all sorted out, or are you still having a problem?
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sorry RagDyeR , I may have misundertstood what you meant.
    >
    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls.
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  77. 09-06-2005, 04:05 AM #77
    RagDyeR
    Guest

    Re: Number of paychecks in a month?

    Would you like me to send you my test sheet?

    If so, post back with your address in pseudo text so the spammers can't
    decipher it.
    --

    Regards,

    RD
    ----------------------------------------------------------------------------
    -------------------
    Please keep all correspondence within the Group, so all may benefit !
    ----------------------------------------------------------------------------
    -------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...

    I still don't have the answer I need.


    --
    jkh1978
    ------------------------------------------------------------------------
    jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    View this thread: http://www.excelforum.com/showthread...hreadid=397278


    Reply With QuoteRegister To Reply
  78. 09-06-2005, 04:05 AM #78
    Sandy Mann
    Guest

    Re: Number of paychecks in a month?

    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls

    That is because I failed to check if the first Friday was a pay day. With
    any pay Friday in D1 try:

    =IF((AND(MOD(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+2)-D1,14)=0,MONTH(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+2)+28)=MONTH(A1))),"3
    paychecks","2 paychecks")

    (Shamlessly stealing a formula for the 1st Friday form Daniel.M this time
    <g>)

    --
    HTH

    Sandy
    [email protected]
    Replace@mailinator with @tiscali.co.uk


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sorry RagDyeR , I may have misundertstood what you meant.
    >
    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls.
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    > http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  79. 09-06-2005, 04:05 AM #79
    RagDyer
    Guest

    Re: Number of paychecks in a month?

    Sample test sheet is on the way.
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sandy Mann, I tried yours but only got 2 for every month
    >
    > Please send a test spreadsheet to jkh1978 AT hotmail.com
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  80. 09-06-2005, 05:05 AM #80
    RagDyer
    Guest

    Re: Number of paychecks in a month?

    Did you get it all sorted out, or are you still having a problem?
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sorry RagDyeR , I may have misundertstood what you meant.
    >
    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls.
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  81. 09-06-2005, 05:05 AM #81
    RagDyeR
    Guest

    Re: Number of paychecks in a month?

    Would you like me to send you my test sheet?

    If so, post back with your address in pseudo text so the spammers can't
    decipher it.
    --

    Regards,

    RD
    ----------------------------------------------------------------------------
    -------------------
    Please keep all correspondence within the Group, so all may benefit !
    ----------------------------------------------------------------------------
    -------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...

    I still don't have the answer I need.


    --
    jkh1978
    ------------------------------------------------------------------------
    jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    View this thread: http://www.excelforum.com/showthread...hreadid=397278


    Reply With QuoteRegister To Reply
  82. 09-06-2005, 05:05 AM #82
    Sandy Mann
    Guest

    Re: Number of paychecks in a month?

    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls

    That is because I failed to check if the first Friday was a pay day. With
    any pay Friday in D1 try:

    =IF((AND(MOD(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+2)-D1,14)=0,MONTH(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+2)+28)=MONTH(A1))),"3
    paychecks","2 paychecks")

    (Shamlessly stealing a formula for the 1st Friday form Daniel.M this time
    <g>)

    --
    HTH

    Sandy
    [email protected]
    Replace@mailinator with @tiscali.co.uk


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sorry RagDyeR , I may have misundertstood what you meant.
    >
    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls.
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    > http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  83. 09-06-2005, 05:05 AM #83
    RagDyer
    Guest

    Re: Number of paychecks in a month?

    Sample test sheet is on the way.
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sandy Mann, I tried yours but only got 2 for every month
    >
    > Please send a test spreadsheet to jkh1978 AT hotmail.com
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  84. 09-06-2005, 06:05 AM #84
    Bob Phillips
    Guest

    Re: Number of paychecks in a month?

    Nor do we. If you get paid in the final fri of a 4 fri month, you will only
    get 2 the following month even if it has 5 fri.

    It all depends upon where you start from, only you know that.

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > In my spreadsheet, I have a column for each month, 8/1/05, 9/1/05,
    > etc...
    >
    > What I want to know is, how many paychecks will I receive each month?
    >
    > I get paid every other week on a Friday.
    >
    > My first attempt with help from reading other posts was to do this
    >
    > =(4+(DAY(D1-DAY(D1)+1-WEEKDAY(D1-DAY(D1+5))+35)>7))/2
    >
    > To get the number of fridays, divided by 2. But that returns 2.5 in
    > some months, and I dont know which of these 4 months each year gets me
    > the extra paycheck.
    >
    > Thanks for the help
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  85. 09-06-2005, 06:05 AM #85
    RagDyeR
    Guest

    Re: Number of paychecks in a month?

    One way.

    You get paid every 14 days ... right?

    Put the date of your first paycheck in say Z1.
    In Z2 enter
    =Z1+14
    And copy down, giving you a list of paydays.

    Then enter this formula in the appropriate row in say Column A for January,
    and copy across for each month:

    =SUMPRODUCT(--(TEXT($Z$1:$Z$26,"mm-yy")=TEXT(A1,"mm-yy")))

    --

    HTH,

    RD
    =====================================================
    Please keep all correspondence within the Group, so all may benefit!
    =====================================================

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...

    That's part of what I'm stuggling with... how do I get that fact into
    the formula?

    September is a 3 paycheck month.


    --
    jkh1978
    ------------------------------------------------------------------------
    jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    View this thread: http://www.excelforum.com/showthread...hreadid=397278


    Reply With QuoteRegister To Reply
  86. 09-06-2005, 06:05 AM #86
    Ragdyer
    Guest

    Re: Number of paychecks in a month?

    The formula that I suggested will count the paychecks in each month and
    display that count wherever you insert the formula.
    The formula is set to be copied to each column, and read the column date
    from the top row that column, and compare that date to the list of your
    paydays that I directed you to create, in an "out-of-the-way" location of
    your sheet.

    Would you care to try it before commenting that it doesn't do what you wish?
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------
    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Thanks for the reply... but thats not what I need. I need to know the
    > number of paychecks in a month.
    >
    > Some months have 5 fridays, so its unclear if its a month that i get 2
    > or 3 paychecks. What forumula would tell me this?
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  87. 09-06-2005, 06:05 AM #87
    Bob Phillips
    Guest

    Re: Number of paychecks in a month?

    Well I tried it and it tells me that info.

    You do not to setup the table the start with, but it's fine then.

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Thanks for the reply... but thats not what I need. I need to know the
    > number of paychecks in a month.
    >
    > Some months have 5 fridays, so its unclear if its a month that i get 2
    > or 3 paychecks. What forumula would tell me this?
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  88. 09-06-2005, 06:05 AM #88
    Sandy Mann
    Guest

    Re: Number of paychecks in a month?

    One way is to check is the Friday 28 days ahead of the 1st Friday in the
    month is still in the same month.

    Adapting a formula for the 1st Friday originally posted by Ron Rosenfeld
    almost exactly a year ago try:

    =IF(MONTH(DATE(YEAR(A1),MONTH(A1),1)+7-WEEKDAY(DATE(YEAR(A1),MONTH(A1),2))+28)=MONTH(A1),"3
    paychecks","2 paychecks")

    With any date in the target month in A1
    --
    HTH

    Sandy
    [email protected]
    Replace@mailinator with @tiscali.co.uk


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > In my spreadsheet, I have a column for each month, 8/1/05, 9/1/05,
    > etc...
    >
    > What I want to know is, how many paychecks will I receive each month?
    >
    > I get paid every other week on a Friday.
    >
    > My first attempt with help from reading other posts was to do this
    >
    > =(4+(DAY(D1-DAY(D1)+1-WEEKDAY(D1-DAY(D1+5))+35)>7))/2
    >
    > To get the number of fridays, divided by 2. But that returns 2.5 in
    > some months, and I dont know which of these 4 months each year gets me
    > the extra paycheck.
    >
    > Thanks for the help
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    > http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >



    Reply With QuoteRegister To Reply
  89. 09-06-2005, 06:05 AM #89
    RagDyer
    Guest

    Re: Number of paychecks in a month?

    Did you get it all sorted out, or are you still having a problem?
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sorry RagDyeR , I may have misundertstood what you meant.
    >
    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls.
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  90. 09-06-2005, 06:05 AM #90
    RagDyeR
    Guest

    Re: Number of paychecks in a month?

    Would you like me to send you my test sheet?

    If so, post back with your address in pseudo text so the spammers can't
    decipher it.
    --

    Regards,

    RD
    ----------------------------------------------------------------------------
    -------------------
    Please keep all correspondence within the Group, so all may benefit !
    ----------------------------------------------------------------------------
    -------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...

    I still don't have the answer I need.


    --
    jkh1978
    ------------------------------------------------------------------------
    jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    View this thread: http://www.excelforum.com/showthread...hreadid=397278


    Reply With QuoteRegister To Reply
  91. 09-06-2005, 06:05 AM #91
    Sandy Mann
    Guest

    Re: Number of paychecks in a month?

    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls

    That is because I failed to check if the first Friday was a pay day. With
    any pay Friday in D1 try:

    =IF((AND(MOD(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+2)-D1,14)=0,MONTH(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+2)+28)=MONTH(A1))),"3
    paychecks","2 paychecks")

    (Shamlessly stealing a formula for the 1st Friday form Daniel.M this time
    <g>)

    --
    HTH

    Sandy
    [email protected]
    Replace@mailinator with @tiscali.co.uk


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sorry RagDyeR , I may have misundertstood what you meant.
    >
    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls.
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    > http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  92. 09-06-2005, 06:05 AM #92
    RagDyer
    Guest

    Re: Number of paychecks in a month?

    Sample test sheet is on the way.
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sandy Mann, I tried yours but only got 2 for every month
    >
    > Please send a test spreadsheet to jkh1978 AT hotmail.com
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  93. 09-06-2005, 07:05 AM #93
    Bob Phillips
    Guest

    Re: Number of paychecks in a month?

    Nor do we. If you get paid in the final fri of a 4 fri month, you will only
    get 2 the following month even if it has 5 fri.

    It all depends upon where you start from, only you know that.

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > In my spreadsheet, I have a column for each month, 8/1/05, 9/1/05,
    > etc...
    >
    > What I want to know is, how many paychecks will I receive each month?
    >
    > I get paid every other week on a Friday.
    >
    > My first attempt with help from reading other posts was to do this
    >
    > =(4+(DAY(D1-DAY(D1)+1-WEEKDAY(D1-DAY(D1+5))+35)>7))/2
    >
    > To get the number of fridays, divided by 2. But that returns 2.5 in
    > some months, and I dont know which of these 4 months each year gets me
    > the extra paycheck.
    >
    > Thanks for the help
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  94. 09-06-2005, 07:05 AM #94
    RagDyeR
    Guest

    Re: Number of paychecks in a month?

    One way.

    You get paid every 14 days ... right?

    Put the date of your first paycheck in say Z1.
    In Z2 enter
    =Z1+14
    And copy down, giving you a list of paydays.

    Then enter this formula in the appropriate row in say Column A for January,
    and copy across for each month:

    =SUMPRODUCT(--(TEXT($Z$1:$Z$26,"mm-yy")=TEXT(A1,"mm-yy")))

    --

    HTH,

    RD
    =====================================================
    Please keep all correspondence within the Group, so all may benefit!
    =====================================================

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...

    That's part of what I'm stuggling with... how do I get that fact into
    the formula?

    September is a 3 paycheck month.


    --
    jkh1978
    ------------------------------------------------------------------------
    jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    View this thread: http://www.excelforum.com/showthread...hreadid=397278


    Reply With QuoteRegister To Reply
  95. 09-06-2005, 07:05 AM #95
    Ragdyer
    Guest

    Re: Number of paychecks in a month?

    The formula that I suggested will count the paychecks in each month and
    display that count wherever you insert the formula.
    The formula is set to be copied to each column, and read the column date
    from the top row that column, and compare that date to the list of your
    paydays that I directed you to create, in an "out-of-the-way" location of
    your sheet.

    Would you care to try it before commenting that it doesn't do what you wish?
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------
    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Thanks for the reply... but thats not what I need. I need to know the
    > number of paychecks in a month.
    >
    > Some months have 5 fridays, so its unclear if its a month that i get 2
    > or 3 paychecks. What forumula would tell me this?
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  96. 09-06-2005, 07:05 AM #96
    Bob Phillips
    Guest

    Re: Number of paychecks in a month?

    Well I tried it and it tells me that info.

    You do not to setup the table the start with, but it's fine then.

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Thanks for the reply... but thats not what I need. I need to know the
    > number of paychecks in a month.
    >
    > Some months have 5 fridays, so its unclear if its a month that i get 2
    > or 3 paychecks. What forumula would tell me this?
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  97. 09-06-2005, 07:05 AM #97
    Sandy Mann
    Guest

    Re: Number of paychecks in a month?

    One way is to check is the Friday 28 days ahead of the 1st Friday in the
    month is still in the same month.

    Adapting a formula for the 1st Friday originally posted by Ron Rosenfeld
    almost exactly a year ago try:

    =IF(MONTH(DATE(YEAR(A1),MONTH(A1),1)+7-WEEKDAY(DATE(YEAR(A1),MONTH(A1),2))+28)=MONTH(A1),"3
    paychecks","2 paychecks")

    With any date in the target month in A1
    --
    HTH

    Sandy
    [email protected]
    Replace@mailinator with @tiscali.co.uk


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > In my spreadsheet, I have a column for each month, 8/1/05, 9/1/05,
    > etc...
    >
    > What I want to know is, how many paychecks will I receive each month?
    >
    > I get paid every other week on a Friday.
    >
    > My first attempt with help from reading other posts was to do this
    >
    > =(4+(DAY(D1-DAY(D1)+1-WEEKDAY(D1-DAY(D1+5))+35)>7))/2
    >
    > To get the number of fridays, divided by 2. But that returns 2.5 in
    > some months, and I dont know which of these 4 months each year gets me
    > the extra paycheck.
    >
    > Thanks for the help
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    > http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >



    Reply With QuoteRegister To Reply
  98. 09-06-2005, 07:05 AM #98
    RagDyer
    Guest

    Re: Number of paychecks in a month?

    Did you get it all sorted out, or are you still having a problem?
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sorry RagDyeR , I may have misundertstood what you meant.
    >
    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls.
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  99. 09-06-2005, 07:05 AM #99
    RagDyeR
    Guest

    Re: Number of paychecks in a month?

    Would you like me to send you my test sheet?

    If so, post back with your address in pseudo text so the spammers can't
    decipher it.
    --

    Regards,

    RD
    ----------------------------------------------------------------------------
    -------------------
    Please keep all correspondence within the Group, so all may benefit !
    ----------------------------------------------------------------------------
    -------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...

    I still don't have the answer I need.


    --
    jkh1978
    ------------------------------------------------------------------------
    jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    View this thread: http://www.excelforum.com/showthread...hreadid=397278


    Reply With QuoteRegister To Reply
  100. 09-06-2005, 07:05 AM #100
    Sandy Mann
    Guest

    Re: Number of paychecks in a month?

    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls

    That is because I failed to check if the first Friday was a pay day. With
    any pay Friday in D1 try:

    =IF((AND(MOD(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+2)-D1,14)=0,MONTH(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+2)+28)=MONTH(A1))),"3
    paychecks","2 paychecks")

    (Shamlessly stealing a formula for the 1st Friday form Daniel.M this time
    <g>)

    --
    HTH

    Sandy
    [email protected]
    Replace@mailinator with @tiscali.co.uk


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sorry RagDyeR , I may have misundertstood what you meant.
    >
    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls.
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    > http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  101. 09-06-2005, 07:05 AM #101
    RagDyer
    Guest

    Re: Number of paychecks in a month?

    Sample test sheet is on the way.
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sandy Mann, I tried yours but only got 2 for every month
    >
    > Please send a test spreadsheet to jkh1978 AT hotmail.com
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  102. 09-06-2005, 09:05 AM #102
    Bob Phillips
    Guest

    Re: Number of paychecks in a month?

    Nor do we. If you get paid in the final fri of a 4 fri month, you will only
    get 2 the following month even if it has 5 fri.

    It all depends upon where you start from, only you know that.

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > In my spreadsheet, I have a column for each month, 8/1/05, 9/1/05,
    > etc...
    >
    > What I want to know is, how many paychecks will I receive each month?
    >
    > I get paid every other week on a Friday.
    >
    > My first attempt with help from reading other posts was to do this
    >
    > =(4+(DAY(D1-DAY(D1)+1-WEEKDAY(D1-DAY(D1+5))+35)>7))/2
    >
    > To get the number of fridays, divided by 2. But that returns 2.5 in
    > some months, and I dont know which of these 4 months each year gets me
    > the extra paycheck.
    >
    > Thanks for the help
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  103. 09-06-2005, 09:05 AM #103
    RagDyeR
    Guest

    Re: Number of paychecks in a month?

    One way.

    You get paid every 14 days ... right?

    Put the date of your first paycheck in say Z1.
    In Z2 enter
    =Z1+14
    And copy down, giving you a list of paydays.

    Then enter this formula in the appropriate row in say Column A for January,
    and copy across for each month:

    =SUMPRODUCT(--(TEXT($Z$1:$Z$26,"mm-yy")=TEXT(A1,"mm-yy")))

    --

    HTH,

    RD
    =====================================================
    Please keep all correspondence within the Group, so all may benefit!
    =====================================================

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...

    That's part of what I'm stuggling with... how do I get that fact into
    the formula?

    September is a 3 paycheck month.


    --
    jkh1978
    ------------------------------------------------------------------------
    jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    View this thread: http://www.excelforum.com/showthread...hreadid=397278


    Reply With QuoteRegister To Reply
  104. 09-06-2005, 09:05 AM #104
    Ragdyer
    Guest

    Re: Number of paychecks in a month?

    The formula that I suggested will count the paychecks in each month and
    display that count wherever you insert the formula.
    The formula is set to be copied to each column, and read the column date
    from the top row that column, and compare that date to the list of your
    paydays that I directed you to create, in an "out-of-the-way" location of
    your sheet.

    Would you care to try it before commenting that it doesn't do what you wish?
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------
    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Thanks for the reply... but thats not what I need. I need to know the
    > number of paychecks in a month.
    >
    > Some months have 5 fridays, so its unclear if its a month that i get 2
    > or 3 paychecks. What forumula would tell me this?
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  105. 09-06-2005, 09:05 AM #105
    Bob Phillips
    Guest

    Re: Number of paychecks in a month?

    Well I tried it and it tells me that info.

    You do not to setup the table the start with, but it's fine then.

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Thanks for the reply... but thats not what I need. I need to know the
    > number of paychecks in a month.
    >
    > Some months have 5 fridays, so its unclear if its a month that i get 2
    > or 3 paychecks. What forumula would tell me this?
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  106. 09-06-2005, 09:05 AM #106
    Sandy Mann
    Guest

    Re: Number of paychecks in a month?

    One way is to check is the Friday 28 days ahead of the 1st Friday in the
    month is still in the same month.

    Adapting a formula for the 1st Friday originally posted by Ron Rosenfeld
    almost exactly a year ago try:

    =IF(MONTH(DATE(YEAR(A1),MONTH(A1),1)+7-WEEKDAY(DATE(YEAR(A1),MONTH(A1),2))+28)=MONTH(A1),"3
    paychecks","2 paychecks")

    With any date in the target month in A1
    --
    HTH

    Sandy
    [email protected]
    Replace@mailinator with @tiscali.co.uk


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > In my spreadsheet, I have a column for each month, 8/1/05, 9/1/05,
    > etc...
    >
    > What I want to know is, how many paychecks will I receive each month?
    >
    > I get paid every other week on a Friday.
    >
    > My first attempt with help from reading other posts was to do this
    >
    > =(4+(DAY(D1-DAY(D1)+1-WEEKDAY(D1-DAY(D1+5))+35)>7))/2
    >
    > To get the number of fridays, divided by 2. But that returns 2.5 in
    > some months, and I dont know which of these 4 months each year gets me
    > the extra paycheck.
    >
    > Thanks for the help
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    > http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >



    Reply With QuoteRegister To Reply
  107. 09-06-2005, 09:05 AM #107
    RagDyer
    Guest

    Re: Number of paychecks in a month?

    Did you get it all sorted out, or are you still having a problem?
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sorry RagDyeR , I may have misundertstood what you meant.
    >
    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls.
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  108. 09-06-2005, 09:05 AM #108
    RagDyeR
    Guest

    Re: Number of paychecks in a month?

    Would you like me to send you my test sheet?

    If so, post back with your address in pseudo text so the spammers can't
    decipher it.
    --

    Regards,

    RD
    ----------------------------------------------------------------------------
    -------------------
    Please keep all correspondence within the Group, so all may benefit !
    ----------------------------------------------------------------------------
    -------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...

    I still don't have the answer I need.


    --
    jkh1978
    ------------------------------------------------------------------------
    jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    View this thread: http://www.excelforum.com/showthread...hreadid=397278


    Reply With QuoteRegister To Reply
  109. 09-06-2005, 09:05 AM #109
    Sandy Mann
    Guest

    Re: Number of paychecks in a month?

    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls

    That is because I failed to check if the first Friday was a pay day. With
    any pay Friday in D1 try:

    =IF((AND(MOD(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+2)-D1,14)=0,MONTH(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+2)+28)=MONTH(A1))),"3
    paychecks","2 paychecks")

    (Shamlessly stealing a formula for the 1st Friday form Daniel.M this time
    <g>)

    --
    HTH

    Sandy
    [email protected]
    Replace@mailinator with @tiscali.co.uk


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sorry RagDyeR , I may have misundertstood what you meant.
    >
    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls.
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    > http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  110. 09-06-2005, 09:05 AM #110
    RagDyer
    Guest

    Re: Number of paychecks in a month?

    Sample test sheet is on the way.
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sandy Mann, I tried yours but only got 2 for every month
    >
    > Please send a test spreadsheet to jkh1978 AT hotmail.com
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  111. 09-06-2005, 10:05 AM #111
    Bob Phillips
    Guest

    Re: Number of paychecks in a month?

    Nor do we. If you get paid in the final fri of a 4 fri month, you will only
    get 2 the following month even if it has 5 fri.

    It all depends upon where you start from, only you know that.

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > In my spreadsheet, I have a column for each month, 8/1/05, 9/1/05,
    > etc...
    >
    > What I want to know is, how many paychecks will I receive each month?
    >
    > I get paid every other week on a Friday.
    >
    > My first attempt with help from reading other posts was to do this
    >
    > =(4+(DAY(D1-DAY(D1)+1-WEEKDAY(D1-DAY(D1+5))+35)>7))/2
    >
    > To get the number of fridays, divided by 2. But that returns 2.5 in
    > some months, and I dont know which of these 4 months each year gets me
    > the extra paycheck.
    >
    > Thanks for the help
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  112. 09-06-2005, 10:05 AM #112
    RagDyeR
    Guest

    Re: Number of paychecks in a month?

    One way.

    You get paid every 14 days ... right?

    Put the date of your first paycheck in say Z1.
    In Z2 enter
    =Z1+14
    And copy down, giving you a list of paydays.

    Then enter this formula in the appropriate row in say Column A for January,
    and copy across for each month:

    =SUMPRODUCT(--(TEXT($Z$1:$Z$26,"mm-yy")=TEXT(A1,"mm-yy")))

    --

    HTH,

    RD
    =====================================================
    Please keep all correspondence within the Group, so all may benefit!
    =====================================================

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...

    That's part of what I'm stuggling with... how do I get that fact into
    the formula?

    September is a 3 paycheck month.


    --
    jkh1978
    ------------------------------------------------------------------------
    jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    View this thread: http://www.excelforum.com/showthread...hreadid=397278


    Reply With QuoteRegister To Reply
  113. 09-06-2005, 10:05 AM #113
    Ragdyer
    Guest

    Re: Number of paychecks in a month?

    The formula that I suggested will count the paychecks in each month and
    display that count wherever you insert the formula.
    The formula is set to be copied to each column, and read the column date
    from the top row that column, and compare that date to the list of your
    paydays that I directed you to create, in an "out-of-the-way" location of
    your sheet.

    Would you care to try it before commenting that it doesn't do what you wish?
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------
    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Thanks for the reply... but thats not what I need. I need to know the
    > number of paychecks in a month.
    >
    > Some months have 5 fridays, so its unclear if its a month that i get 2
    > or 3 paychecks. What forumula would tell me this?
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  114. 09-06-2005, 10:05 AM #114
    Bob Phillips
    Guest

    Re: Number of paychecks in a month?

    Well I tried it and it tells me that info.

    You do not to setup the table the start with, but it's fine then.

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Thanks for the reply... but thats not what I need. I need to know the
    > number of paychecks in a month.
    >
    > Some months have 5 fridays, so its unclear if its a month that i get 2
    > or 3 paychecks. What forumula would tell me this?
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  115. 09-06-2005, 10:05 AM #115
    Sandy Mann
    Guest

    Re: Number of paychecks in a month?

    One way is to check is the Friday 28 days ahead of the 1st Friday in the
    month is still in the same month.

    Adapting a formula for the 1st Friday originally posted by Ron Rosenfeld
    almost exactly a year ago try:

    =IF(MONTH(DATE(YEAR(A1),MONTH(A1),1)+7-WEEKDAY(DATE(YEAR(A1),MONTH(A1),2))+28)=MONTH(A1),"3
    paychecks","2 paychecks")

    With any date in the target month in A1
    --
    HTH

    Sandy
    [email protected]
    Replace@mailinator with @tiscali.co.uk


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > In my spreadsheet, I have a column for each month, 8/1/05, 9/1/05,
    > etc...
    >
    > What I want to know is, how many paychecks will I receive each month?
    >
    > I get paid every other week on a Friday.
    >
    > My first attempt with help from reading other posts was to do this
    >
    > =(4+(DAY(D1-DAY(D1)+1-WEEKDAY(D1-DAY(D1+5))+35)>7))/2
    >
    > To get the number of fridays, divided by 2. But that returns 2.5 in
    > some months, and I dont know which of these 4 months each year gets me
    > the extra paycheck.
    >
    > Thanks for the help
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    > http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >



    Reply With QuoteRegister To Reply
  116. 09-06-2005, 10:05 AM #116
    RagDyer
    Guest

    Re: Number of paychecks in a month?

    Did you get it all sorted out, or are you still having a problem?
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sorry RagDyeR , I may have misundertstood what you meant.
    >
    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls.
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  117. 09-06-2005, 10:05 AM #117
    RagDyeR
    Guest

    Re: Number of paychecks in a month?

    Would you like me to send you my test sheet?

    If so, post back with your address in pseudo text so the spammers can't
    decipher it.
    --

    Regards,

    RD
    ----------------------------------------------------------------------------
    -------------------
    Please keep all correspondence within the Group, so all may benefit !
    ----------------------------------------------------------------------------
    -------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...

    I still don't have the answer I need.


    --
    jkh1978
    ------------------------------------------------------------------------
    jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    View this thread: http://www.excelforum.com/showthread...hreadid=397278


    Reply With QuoteRegister To Reply
  118. 09-06-2005, 10:05 AM #118
    Sandy Mann
    Guest

    Re: Number of paychecks in a month?

    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls

    That is because I failed to check if the first Friday was a pay day. With
    any pay Friday in D1 try:

    =IF((AND(MOD(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+2)-D1,14)=0,MONTH(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+2)+28)=MONTH(A1))),"3
    paychecks","2 paychecks")

    (Shamlessly stealing a formula for the 1st Friday form Daniel.M this time
    <g>)

    --
    HTH

    Sandy
    [email protected]
    Replace@mailinator with @tiscali.co.uk


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sorry RagDyeR , I may have misundertstood what you meant.
    >
    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls.
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    > http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  119. 09-06-2005, 10:05 AM #119
    RagDyer
    Guest

    Re: Number of paychecks in a month?

    Sample test sheet is on the way.
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sandy Mann, I tried yours but only got 2 for every month
    >
    > Please send a test spreadsheet to jkh1978 AT hotmail.com
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  120. 09-06-2005, 11:05 AM #120
    Bob Phillips
    Guest

    Re: Number of paychecks in a month?

    Nor do we. If you get paid in the final fri of a 4 fri month, you will only
    get 2 the following month even if it has 5 fri.

    It all depends upon where you start from, only you know that.

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > In my spreadsheet, I have a column for each month, 8/1/05, 9/1/05,
    > etc...
    >
    > What I want to know is, how many paychecks will I receive each month?
    >
    > I get paid every other week on a Friday.
    >
    > My first attempt with help from reading other posts was to do this
    >
    > =(4+(DAY(D1-DAY(D1)+1-WEEKDAY(D1-DAY(D1+5))+35)>7))/2
    >
    > To get the number of fridays, divided by 2. But that returns 2.5 in
    > some months, and I dont know which of these 4 months each year gets me
    > the extra paycheck.
    >
    > Thanks for the help
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  121. 09-06-2005, 11:05 AM #121
    RagDyeR
    Guest

    Re: Number of paychecks in a month?

    One way.

    You get paid every 14 days ... right?

    Put the date of your first paycheck in say Z1.
    In Z2 enter
    =Z1+14
    And copy down, giving you a list of paydays.

    Then enter this formula in the appropriate row in say Column A for January,
    and copy across for each month:

    =SUMPRODUCT(--(TEXT($Z$1:$Z$26,"mm-yy")=TEXT(A1,"mm-yy")))

    --

    HTH,

    RD
    =====================================================
    Please keep all correspondence within the Group, so all may benefit!
    =====================================================

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...

    That's part of what I'm stuggling with... how do I get that fact into
    the formula?

    September is a 3 paycheck month.


    --
    jkh1978
    ------------------------------------------------------------------------
    jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    View this thread: http://www.excelforum.com/showthread...hreadid=397278


    Reply With QuoteRegister To Reply
  122. 09-06-2005, 11:05 AM #122
    Ragdyer
    Guest

    Re: Number of paychecks in a month?

    The formula that I suggested will count the paychecks in each month and
    display that count wherever you insert the formula.
    The formula is set to be copied to each column, and read the column date
    from the top row that column, and compare that date to the list of your
    paydays that I directed you to create, in an "out-of-the-way" location of
    your sheet.

    Would you care to try it before commenting that it doesn't do what you wish?
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------
    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Thanks for the reply... but thats not what I need. I need to know the
    > number of paychecks in a month.
    >
    > Some months have 5 fridays, so its unclear if its a month that i get 2
    > or 3 paychecks. What forumula would tell me this?
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  123. 09-06-2005, 11:05 AM #123
    Bob Phillips
    Guest

    Re: Number of paychecks in a month?

    Well I tried it and it tells me that info.

    You do not to setup the table the start with, but it's fine then.

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Thanks for the reply... but thats not what I need. I need to know the
    > number of paychecks in a month.
    >
    > Some months have 5 fridays, so its unclear if its a month that i get 2
    > or 3 paychecks. What forumula would tell me this?
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  124. 09-06-2005, 11:05 AM #124
    Sandy Mann
    Guest

    Re: Number of paychecks in a month?

    One way is to check is the Friday 28 days ahead of the 1st Friday in the
    month is still in the same month.

    Adapting a formula for the 1st Friday originally posted by Ron Rosenfeld
    almost exactly a year ago try:

    =IF(MONTH(DATE(YEAR(A1),MONTH(A1),1)+7-WEEKDAY(DATE(YEAR(A1),MONTH(A1),2))+28)=MONTH(A1),"3
    paychecks","2 paychecks")

    With any date in the target month in A1
    --
    HTH

    Sandy
    [email protected]
    Replace@mailinator with @tiscali.co.uk


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > In my spreadsheet, I have a column for each month, 8/1/05, 9/1/05,
    > etc...
    >
    > What I want to know is, how many paychecks will I receive each month?
    >
    > I get paid every other week on a Friday.
    >
    > My first attempt with help from reading other posts was to do this
    >
    > =(4+(DAY(D1-DAY(D1)+1-WEEKDAY(D1-DAY(D1+5))+35)>7))/2
    >
    > To get the number of fridays, divided by 2. But that returns 2.5 in
    > some months, and I dont know which of these 4 months each year gets me
    > the extra paycheck.
    >
    > Thanks for the help
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    > http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >



    Reply With QuoteRegister To Reply
  125. 09-06-2005, 11:05 AM #125
    RagDyer
    Guest

    Re: Number of paychecks in a month?

    Did you get it all sorted out, or are you still having a problem?
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sorry RagDyeR , I may have misundertstood what you meant.
    >
    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls.
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  126. 09-06-2005, 11:05 AM #126
    RagDyeR
    Guest

    Re: Number of paychecks in a month?

    Would you like me to send you my test sheet?

    If so, post back with your address in pseudo text so the spammers can't
    decipher it.
    --

    Regards,

    RD
    ----------------------------------------------------------------------------
    -------------------
    Please keep all correspondence within the Group, so all may benefit !
    ----------------------------------------------------------------------------
    -------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...

    I still don't have the answer I need.


    --
    jkh1978
    ------------------------------------------------------------------------
    jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    View this thread: http://www.excelforum.com/showthread...hreadid=397278


    Reply With QuoteRegister To Reply
  127. 09-06-2005, 11:05 AM #127
    Sandy Mann
    Guest

    Re: Number of paychecks in a month?

    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls

    That is because I failed to check if the first Friday was a pay day. With
    any pay Friday in D1 try:

    =IF((AND(MOD(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+2)-D1,14)=0,MONTH(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+2)+28)=MONTH(A1))),"3
    paychecks","2 paychecks")

    (Shamlessly stealing a formula for the 1st Friday form Daniel.M this time
    <g>)

    --
    HTH

    Sandy
    [email protected]
    Replace@mailinator with @tiscali.co.uk


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sorry RagDyeR , I may have misundertstood what you meant.
    >
    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls.
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    > http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  128. 09-06-2005, 11:05 AM #128
    RagDyer
    Guest

    Re: Number of paychecks in a month?

    Sample test sheet is on the way.
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sandy Mann, I tried yours but only got 2 for every month
    >
    > Please send a test spreadsheet to jkh1978 AT hotmail.com
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  129. 09-06-2005, 12:05 PM #129
    Bob Phillips
    Guest

    Re: Number of paychecks in a month?

    Nor do we. If you get paid in the final fri of a 4 fri month, you will only
    get 2 the following month even if it has 5 fri.

    It all depends upon where you start from, only you know that.

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > In my spreadsheet, I have a column for each month, 8/1/05, 9/1/05,
    > etc...
    >
    > What I want to know is, how many paychecks will I receive each month?
    >
    > I get paid every other week on a Friday.
    >
    > My first attempt with help from reading other posts was to do this
    >
    > =(4+(DAY(D1-DAY(D1)+1-WEEKDAY(D1-DAY(D1+5))+35)>7))/2
    >
    > To get the number of fridays, divided by 2. But that returns 2.5 in
    > some months, and I dont know which of these 4 months each year gets me
    > the extra paycheck.
    >
    > Thanks for the help
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  130. 09-06-2005, 12:05 PM #130
    RagDyeR
    Guest

    Re: Number of paychecks in a month?

    One way.

    You get paid every 14 days ... right?

    Put the date of your first paycheck in say Z1.
    In Z2 enter
    =Z1+14
    And copy down, giving you a list of paydays.

    Then enter this formula in the appropriate row in say Column A for January,
    and copy across for each month:

    =SUMPRODUCT(--(TEXT($Z$1:$Z$26,"mm-yy")=TEXT(A1,"mm-yy")))

    --

    HTH,

    RD
    =====================================================
    Please keep all correspondence within the Group, so all may benefit!
    =====================================================

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...

    That's part of what I'm stuggling with... how do I get that fact into
    the formula?

    September is a 3 paycheck month.


    --
    jkh1978
    ------------------------------------------------------------------------
    jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    View this thread: http://www.excelforum.com/showthread...hreadid=397278


    Reply With QuoteRegister To Reply
  131. 09-06-2005, 12:05 PM #131
    Ragdyer
    Guest

    Re: Number of paychecks in a month?

    The formula that I suggested will count the paychecks in each month and
    display that count wherever you insert the formula.
    The formula is set to be copied to each column, and read the column date
    from the top row that column, and compare that date to the list of your
    paydays that I directed you to create, in an "out-of-the-way" location of
    your sheet.

    Would you care to try it before commenting that it doesn't do what you wish?
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------
    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Thanks for the reply... but thats not what I need. I need to know the
    > number of paychecks in a month.
    >
    > Some months have 5 fridays, so its unclear if its a month that i get 2
    > or 3 paychecks. What forumula would tell me this?
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  132. 09-06-2005, 12:05 PM #132
    Bob Phillips
    Guest

    Re: Number of paychecks in a month?

    Well I tried it and it tells me that info.

    You do not to setup the table the start with, but it's fine then.

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Thanks for the reply... but thats not what I need. I need to know the
    > number of paychecks in a month.
    >
    > Some months have 5 fridays, so its unclear if its a month that i get 2
    > or 3 paychecks. What forumula would tell me this?
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  133. 09-06-2005, 12:05 PM #133
    Sandy Mann
    Guest

    Re: Number of paychecks in a month?

    One way is to check is the Friday 28 days ahead of the 1st Friday in the
    month is still in the same month.

    Adapting a formula for the 1st Friday originally posted by Ron Rosenfeld
    almost exactly a year ago try:

    =IF(MONTH(DATE(YEAR(A1),MONTH(A1),1)+7-WEEKDAY(DATE(YEAR(A1),MONTH(A1),2))+28)=MONTH(A1),"3
    paychecks","2 paychecks")

    With any date in the target month in A1
    --
    HTH

    Sandy
    [email protected]
    Replace@mailinator with @tiscali.co.uk


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > In my spreadsheet, I have a column for each month, 8/1/05, 9/1/05,
    > etc...
    >
    > What I want to know is, how many paychecks will I receive each month?
    >
    > I get paid every other week on a Friday.
    >
    > My first attempt with help from reading other posts was to do this
    >
    > =(4+(DAY(D1-DAY(D1)+1-WEEKDAY(D1-DAY(D1+5))+35)>7))/2
    >
    > To get the number of fridays, divided by 2. But that returns 2.5 in
    > some months, and I dont know which of these 4 months each year gets me
    > the extra paycheck.
    >
    > Thanks for the help
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    > http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >



    Reply With QuoteRegister To Reply
  134. 09-06-2005, 12:05 PM #134
    RagDyer
    Guest

    Re: Number of paychecks in a month?

    Did you get it all sorted out, or are you still having a problem?
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sorry RagDyeR , I may have misundertstood what you meant.
    >
    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls.
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  135. 09-06-2005, 12:05 PM #135
    RagDyeR
    Guest

    Re: Number of paychecks in a month?

    Would you like me to send you my test sheet?

    If so, post back with your address in pseudo text so the spammers can't
    decipher it.
    --

    Regards,

    RD
    ----------------------------------------------------------------------------
    -------------------
    Please keep all correspondence within the Group, so all may benefit !
    ----------------------------------------------------------------------------
    -------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...

    I still don't have the answer I need.


    --
    jkh1978
    ------------------------------------------------------------------------
    jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    View this thread: http://www.excelforum.com/showthread...hreadid=397278


    Reply With QuoteRegister To Reply
  136. 09-06-2005, 12:05 PM #136
    Sandy Mann
    Guest

    Re: Number of paychecks in a month?

    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls

    That is because I failed to check if the first Friday was a pay day. With
    any pay Friday in D1 try:

    =IF((AND(MOD(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+2)-D1,14)=0,MONTH(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+2)+28)=MONTH(A1))),"3
    paychecks","2 paychecks")

    (Shamlessly stealing a formula for the 1st Friday form Daniel.M this time
    <g>)

    --
    HTH

    Sandy
    [email protected]
    Replace@mailinator with @tiscali.co.uk


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sorry RagDyeR , I may have misundertstood what you meant.
    >
    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls.
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    > http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  137. 09-06-2005, 12:05 PM #137
    RagDyer
    Guest

    Re: Number of paychecks in a month?

    Sample test sheet is on the way.
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sandy Mann, I tried yours but only got 2 for every month
    >
    > Please send a test spreadsheet to jkh1978 AT hotmail.com
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  138. 09-06-2005, 02:05 PM #138
    Bob Phillips
    Guest

    Re: Number of paychecks in a month?

    Nor do we. If you get paid in the final fri of a 4 fri month, you will only
    get 2 the following month even if it has 5 fri.

    It all depends upon where you start from, only you know that.

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > In my spreadsheet, I have a column for each month, 8/1/05, 9/1/05,
    > etc...
    >
    > What I want to know is, how many paychecks will I receive each month?
    >
    > I get paid every other week on a Friday.
    >
    > My first attempt with help from reading other posts was to do this
    >
    > =(4+(DAY(D1-DAY(D1)+1-WEEKDAY(D1-DAY(D1+5))+35)>7))/2
    >
    > To get the number of fridays, divided by 2. But that returns 2.5 in
    > some months, and I dont know which of these 4 months each year gets me
    > the extra paycheck.
    >
    > Thanks for the help
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  139. 09-06-2005, 02:05 PM #139
    RagDyeR
    Guest

    Re: Number of paychecks in a month?

    One way.

    You get paid every 14 days ... right?

    Put the date of your first paycheck in say Z1.
    In Z2 enter
    =Z1+14
    And copy down, giving you a list of paydays.

    Then enter this formula in the appropriate row in say Column A for January,
    and copy across for each month:

    =SUMPRODUCT(--(TEXT($Z$1:$Z$26,"mm-yy")=TEXT(A1,"mm-yy")))

    --

    HTH,

    RD
    =====================================================
    Please keep all correspondence within the Group, so all may benefit!
    =====================================================

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...

    That's part of what I'm stuggling with... how do I get that fact into
    the formula?

    September is a 3 paycheck month.


    --
    jkh1978
    ------------------------------------------------------------------------
    jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    View this thread: http://www.excelforum.com/showthread...hreadid=397278


    Reply With QuoteRegister To Reply
  140. 09-06-2005, 02:05 PM #140
    Ragdyer
    Guest

    Re: Number of paychecks in a month?

    The formula that I suggested will count the paychecks in each month and
    display that count wherever you insert the formula.
    The formula is set to be copied to each column, and read the column date
    from the top row that column, and compare that date to the list of your
    paydays that I directed you to create, in an "out-of-the-way" location of
    your sheet.

    Would you care to try it before commenting that it doesn't do what you wish?
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------
    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Thanks for the reply... but thats not what I need. I need to know the
    > number of paychecks in a month.
    >
    > Some months have 5 fridays, so its unclear if its a month that i get 2
    > or 3 paychecks. What forumula would tell me this?
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  141. 09-06-2005, 02:05 PM #141
    Bob Phillips
    Guest

    Re: Number of paychecks in a month?

    Well I tried it and it tells me that info.

    You do not to setup the table the start with, but it's fine then.

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Thanks for the reply... but thats not what I need. I need to know the
    > number of paychecks in a month.
    >
    > Some months have 5 fridays, so its unclear if its a month that i get 2
    > or 3 paychecks. What forumula would tell me this?
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  142. 09-06-2005, 02:05 PM #142
    Sandy Mann
    Guest

    Re: Number of paychecks in a month?

    One way is to check is the Friday 28 days ahead of the 1st Friday in the
    month is still in the same month.

    Adapting a formula for the 1st Friday originally posted by Ron Rosenfeld
    almost exactly a year ago try:

    =IF(MONTH(DATE(YEAR(A1),MONTH(A1),1)+7-WEEKDAY(DATE(YEAR(A1),MONTH(A1),2))+28)=MONTH(A1),"3
    paychecks","2 paychecks")

    With any date in the target month in A1
    --
    HTH

    Sandy
    [email protected]
    Replace@mailinator with @tiscali.co.uk


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > In my spreadsheet, I have a column for each month, 8/1/05, 9/1/05,
    > etc...
    >
    > What I want to know is, how many paychecks will I receive each month?
    >
    > I get paid every other week on a Friday.
    >
    > My first attempt with help from reading other posts was to do this
    >
    > =(4+(DAY(D1-DAY(D1)+1-WEEKDAY(D1-DAY(D1+5))+35)>7))/2
    >
    > To get the number of fridays, divided by 2. But that returns 2.5 in
    > some months, and I dont know which of these 4 months each year gets me
    > the extra paycheck.
    >
    > Thanks for the help
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    > http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >



    Reply With QuoteRegister To Reply
  143. 09-06-2005, 02:05 PM #143
    RagDyer
    Guest

    Re: Number of paychecks in a month?

    Did you get it all sorted out, or are you still having a problem?
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sorry RagDyeR , I may have misundertstood what you meant.
    >
    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls.
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  144. 09-06-2005, 02:05 PM #144
    RagDyeR
    Guest

    Re: Number of paychecks in a month?

    Would you like me to send you my test sheet?

    If so, post back with your address in pseudo text so the spammers can't
    decipher it.
    --

    Regards,

    RD
    ----------------------------------------------------------------------------
    -------------------
    Please keep all correspondence within the Group, so all may benefit !
    ----------------------------------------------------------------------------
    -------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...

    I still don't have the answer I need.


    --
    jkh1978
    ------------------------------------------------------------------------
    jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    View this thread: http://www.excelforum.com/showthread...hreadid=397278


    Reply With QuoteRegister To Reply
  145. 09-06-2005, 02:05 PM #145
    Sandy Mann
    Guest

    Re: Number of paychecks in a month?

    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls

    That is because I failed to check if the first Friday was a pay day. With
    any pay Friday in D1 try:

    =IF((AND(MOD(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+2)-D1,14)=0,MONTH(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+2)+28)=MONTH(A1))),"3
    paychecks","2 paychecks")

    (Shamlessly stealing a formula for the 1st Friday form Daniel.M this time
    <g>)

    --
    HTH

    Sandy
    [email protected]
    Replace@mailinator with @tiscali.co.uk


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sorry RagDyeR , I may have misundertstood what you meant.
    >
    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls.
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    > http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  146. 09-06-2005, 02:05 PM #146
    RagDyer
    Guest

    Re: Number of paychecks in a month?

    Sample test sheet is on the way.
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sandy Mann, I tried yours but only got 2 for every month
    >
    > Please send a test spreadsheet to jkh1978 AT hotmail.com
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  147. 09-06-2005, 03:05 PM #147
    Bob Phillips
    Guest

    Re: Number of paychecks in a month?

    Nor do we. If you get paid in the final fri of a 4 fri month, you will only
    get 2 the following month even if it has 5 fri.

    It all depends upon where you start from, only you know that.

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > In my spreadsheet, I have a column for each month, 8/1/05, 9/1/05,
    > etc...
    >
    > What I want to know is, how many paychecks will I receive each month?
    >
    > I get paid every other week on a Friday.
    >
    > My first attempt with help from reading other posts was to do this
    >
    > =(4+(DAY(D1-DAY(D1)+1-WEEKDAY(D1-DAY(D1+5))+35)>7))/2
    >
    > To get the number of fridays, divided by 2. But that returns 2.5 in
    > some months, and I dont know which of these 4 months each year gets me
    > the extra paycheck.
    >
    > Thanks for the help
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  148. 09-06-2005, 03:05 PM #148
    RagDyeR
    Guest

    Re: Number of paychecks in a month?

    One way.

    You get paid every 14 days ... right?

    Put the date of your first paycheck in say Z1.
    In Z2 enter
    =Z1+14
    And copy down, giving you a list of paydays.

    Then enter this formula in the appropriate row in say Column A for January,
    and copy across for each month:

    =SUMPRODUCT(--(TEXT($Z$1:$Z$26,"mm-yy")=TEXT(A1,"mm-yy")))

    --

    HTH,

    RD
    =====================================================
    Please keep all correspondence within the Group, so all may benefit!
    =====================================================

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...

    That's part of what I'm stuggling with... how do I get that fact into
    the formula?

    September is a 3 paycheck month.


    --
    jkh1978
    ------------------------------------------------------------------------
    jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    View this thread: http://www.excelforum.com/showthread...hreadid=397278


    Reply With QuoteRegister To Reply
  149. 09-06-2005, 03:05 PM #149
    Ragdyer
    Guest

    Re: Number of paychecks in a month?

    The formula that I suggested will count the paychecks in each month and
    display that count wherever you insert the formula.
    The formula is set to be copied to each column, and read the column date
    from the top row that column, and compare that date to the list of your
    paydays that I directed you to create, in an "out-of-the-way" location of
    your sheet.

    Would you care to try it before commenting that it doesn't do what you wish?
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------
    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Thanks for the reply... but thats not what I need. I need to know the
    > number of paychecks in a month.
    >
    > Some months have 5 fridays, so its unclear if its a month that i get 2
    > or 3 paychecks. What forumula would tell me this?
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  150. 09-06-2005, 03:05 PM #150
    Bob Phillips
    Guest

    Re: Number of paychecks in a month?

    Well I tried it and it tells me that info.

    You do not to setup the table the start with, but it's fine then.

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Thanks for the reply... but thats not what I need. I need to know the
    > number of paychecks in a month.
    >
    > Some months have 5 fridays, so its unclear if its a month that i get 2
    > or 3 paychecks. What forumula would tell me this?
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  151. 09-06-2005, 03:05 PM #151
    Sandy Mann
    Guest

    Re: Number of paychecks in a month?

    One way is to check is the Friday 28 days ahead of the 1st Friday in the
    month is still in the same month.

    Adapting a formula for the 1st Friday originally posted by Ron Rosenfeld
    almost exactly a year ago try:

    =IF(MONTH(DATE(YEAR(A1),MONTH(A1),1)+7-WEEKDAY(DATE(YEAR(A1),MONTH(A1),2))+28)=MONTH(A1),"3
    paychecks","2 paychecks")

    With any date in the target month in A1
    --
    HTH

    Sandy
    [email protected]
    Replace@mailinator with @tiscali.co.uk


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > In my spreadsheet, I have a column for each month, 8/1/05, 9/1/05,
    > etc...
    >
    > What I want to know is, how many paychecks will I receive each month?
    >
    > I get paid every other week on a Friday.
    >
    > My first attempt with help from reading other posts was to do this
    >
    > =(4+(DAY(D1-DAY(D1)+1-WEEKDAY(D1-DAY(D1+5))+35)>7))/2
    >
    > To get the number of fridays, divided by 2. But that returns 2.5 in
    > some months, and I dont know which of these 4 months each year gets me
    > the extra paycheck.
    >
    > Thanks for the help
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    > http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >



    Reply With QuoteRegister To Reply
  152. 09-06-2005, 03:05 PM #152
    RagDyer
    Guest

    Re: Number of paychecks in a month?

    Did you get it all sorted out, or are you still having a problem?
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sorry RagDyeR , I may have misundertstood what you meant.
    >
    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls.
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  153. 09-06-2005, 03:05 PM #153
    RagDyeR
    Guest

    Re: Number of paychecks in a month?

    Would you like me to send you my test sheet?

    If so, post back with your address in pseudo text so the spammers can't
    decipher it.
    --

    Regards,

    RD
    ----------------------------------------------------------------------------
    -------------------
    Please keep all correspondence within the Group, so all may benefit !
    ----------------------------------------------------------------------------
    -------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...

    I still don't have the answer I need.


    --
    jkh1978
    ------------------------------------------------------------------------
    jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    View this thread: http://www.excelforum.com/showthread...hreadid=397278


    Reply With QuoteRegister To Reply
  154. 09-06-2005, 03:05 PM #154
    Sandy Mann
    Guest

    Re: Number of paychecks in a month?

    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls

    That is because I failed to check if the first Friday was a pay day. With
    any pay Friday in D1 try:

    =IF((AND(MOD(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+2)-D1,14)=0,MONTH(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+2)+28)=MONTH(A1))),"3
    paychecks","2 paychecks")

    (Shamlessly stealing a formula for the 1st Friday form Daniel.M this time
    <g>)

    --
    HTH

    Sandy
    [email protected]
    Replace@mailinator with @tiscali.co.uk


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sorry RagDyeR , I may have misundertstood what you meant.
    >
    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls.
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    > http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  155. 09-06-2005, 03:05 PM #155
    RagDyer
    Guest

    Re: Number of paychecks in a month?

    Sample test sheet is on the way.
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sandy Mann, I tried yours but only got 2 for every month
    >
    > Please send a test spreadsheet to jkh1978 AT hotmail.com
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  156. 09-06-2005, 04:05 PM #156
    Bob Phillips
    Guest

    Re: Number of paychecks in a month?

    Nor do we. If you get paid in the final fri of a 4 fri month, you will only
    get 2 the following month even if it has 5 fri.

    It all depends upon where you start from, only you know that.

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > In my spreadsheet, I have a column for each month, 8/1/05, 9/1/05,
    > etc...
    >
    > What I want to know is, how many paychecks will I receive each month?
    >
    > I get paid every other week on a Friday.
    >
    > My first attempt with help from reading other posts was to do this
    >
    > =(4+(DAY(D1-DAY(D1)+1-WEEKDAY(D1-DAY(D1+5))+35)>7))/2
    >
    > To get the number of fridays, divided by 2. But that returns 2.5 in
    > some months, and I dont know which of these 4 months each year gets me
    > the extra paycheck.
    >
    > Thanks for the help
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  157. 09-06-2005, 04:05 PM #157
    RagDyeR
    Guest

    Re: Number of paychecks in a month?

    One way.

    You get paid every 14 days ... right?

    Put the date of your first paycheck in say Z1.
    In Z2 enter
    =Z1+14
    And copy down, giving you a list of paydays.

    Then enter this formula in the appropriate row in say Column A for January,
    and copy across for each month:

    =SUMPRODUCT(--(TEXT($Z$1:$Z$26,"mm-yy")=TEXT(A1,"mm-yy")))

    --

    HTH,

    RD
    =====================================================
    Please keep all correspondence within the Group, so all may benefit!
    =====================================================

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...

    That's part of what I'm stuggling with... how do I get that fact into
    the formula?

    September is a 3 paycheck month.


    --
    jkh1978
    ------------------------------------------------------------------------
    jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    View this thread: http://www.excelforum.com/showthread...hreadid=397278


    Reply With QuoteRegister To Reply
  158. 09-06-2005, 04:05 PM #158
    Ragdyer
    Guest

    Re: Number of paychecks in a month?

    The formula that I suggested will count the paychecks in each month and
    display that count wherever you insert the formula.
    The formula is set to be copied to each column, and read the column date
    from the top row that column, and compare that date to the list of your
    paydays that I directed you to create, in an "out-of-the-way" location of
    your sheet.

    Would you care to try it before commenting that it doesn't do what you wish?
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------
    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Thanks for the reply... but thats not what I need. I need to know the
    > number of paychecks in a month.
    >
    > Some months have 5 fridays, so its unclear if its a month that i get 2
    > or 3 paychecks. What forumula would tell me this?
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  159. 09-06-2005, 04:05 PM #159
    Bob Phillips
    Guest

    Re: Number of paychecks in a month?

    Well I tried it and it tells me that info.

    You do not to setup the table the start with, but it's fine then.

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Thanks for the reply... but thats not what I need. I need to know the
    > number of paychecks in a month.
    >
    > Some months have 5 fridays, so its unclear if its a month that i get 2
    > or 3 paychecks. What forumula would tell me this?
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  160. 09-06-2005, 04:05 PM #160
    Sandy Mann
    Guest

    Re: Number of paychecks in a month?

    One way is to check is the Friday 28 days ahead of the 1st Friday in the
    month is still in the same month.

    Adapting a formula for the 1st Friday originally posted by Ron Rosenfeld
    almost exactly a year ago try:

    =IF(MONTH(DATE(YEAR(A1),MONTH(A1),1)+7-WEEKDAY(DATE(YEAR(A1),MONTH(A1),2))+28)=MONTH(A1),"3
    paychecks","2 paychecks")

    With any date in the target month in A1
    --
    HTH

    Sandy
    [email protected]
    Replace@mailinator with @tiscali.co.uk


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > In my spreadsheet, I have a column for each month, 8/1/05, 9/1/05,
    > etc...
    >
    > What I want to know is, how many paychecks will I receive each month?
    >
    > I get paid every other week on a Friday.
    >
    > My first attempt with help from reading other posts was to do this
    >
    > =(4+(DAY(D1-DAY(D1)+1-WEEKDAY(D1-DAY(D1+5))+35)>7))/2
    >
    > To get the number of fridays, divided by 2. But that returns 2.5 in
    > some months, and I dont know which of these 4 months each year gets me
    > the extra paycheck.
    >
    > Thanks for the help
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    > http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >



    Reply With QuoteRegister To Reply
  161. 09-06-2005, 04:05 PM #161
    RagDyer
    Guest

    Re: Number of paychecks in a month?

    Did you get it all sorted out, or are you still having a problem?
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sorry RagDyeR , I may have misundertstood what you meant.
    >
    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls.
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  162. 09-06-2005, 04:05 PM #162
    RagDyeR
    Guest

    Re: Number of paychecks in a month?

    Would you like me to send you my test sheet?

    If so, post back with your address in pseudo text so the spammers can't
    decipher it.
    --

    Regards,

    RD
    ----------------------------------------------------------------------------
    -------------------
    Please keep all correspondence within the Group, so all may benefit !
    ----------------------------------------------------------------------------
    -------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...

    I still don't have the answer I need.


    --
    jkh1978
    ------------------------------------------------------------------------
    jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    View this thread: http://www.excelforum.com/showthread...hreadid=397278


    Reply With QuoteRegister To Reply
  163. 09-06-2005, 04:05 PM #163
    Sandy Mann
    Guest

    Re: Number of paychecks in a month?

    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls

    That is because I failed to check if the first Friday was a pay day. With
    any pay Friday in D1 try:

    =IF((AND(MOD(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+2)-D1,14)=0,MONTH(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+2)+28)=MONTH(A1))),"3
    paychecks","2 paychecks")

    (Shamlessly stealing a formula for the 1st Friday form Daniel.M this time
    <g>)

    --
    HTH

    Sandy
    [email protected]
    Replace@mailinator with @tiscali.co.uk


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sorry RagDyeR , I may have misundertstood what you meant.
    >
    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls.
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    > http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  164. 09-06-2005, 04:05 PM #164
    RagDyer
    Guest

    Re: Number of paychecks in a month?

    Sample test sheet is on the way.
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sandy Mann, I tried yours but only got 2 for every month
    >
    > Please send a test spreadsheet to jkh1978 AT hotmail.com
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  165. 09-06-2005, 05:05 PM #165
    Bob Phillips
    Guest

    Re: Number of paychecks in a month?

    Nor do we. If you get paid in the final fri of a 4 fri month, you will only
    get 2 the following month even if it has 5 fri.

    It all depends upon where you start from, only you know that.

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > In my spreadsheet, I have a column for each month, 8/1/05, 9/1/05,
    > etc...
    >
    > What I want to know is, how many paychecks will I receive each month?
    >
    > I get paid every other week on a Friday.
    >
    > My first attempt with help from reading other posts was to do this
    >
    > =(4+(DAY(D1-DAY(D1)+1-WEEKDAY(D1-DAY(D1+5))+35)>7))/2
    >
    > To get the number of fridays, divided by 2. But that returns 2.5 in
    > some months, and I dont know which of these 4 months each year gets me
    > the extra paycheck.
    >
    > Thanks for the help
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  166. 09-06-2005, 05:05 PM #166
    RagDyeR
    Guest

    Re: Number of paychecks in a month?

    One way.

    You get paid every 14 days ... right?

    Put the date of your first paycheck in say Z1.
    In Z2 enter
    =Z1+14
    And copy down, giving you a list of paydays.

    Then enter this formula in the appropriate row in say Column A for January,
    and copy across for each month:

    =SUMPRODUCT(--(TEXT($Z$1:$Z$26,"mm-yy")=TEXT(A1,"mm-yy")))

    --

    HTH,

    RD
    =====================================================
    Please keep all correspondence within the Group, so all may benefit!
    =====================================================

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...

    That's part of what I'm stuggling with... how do I get that fact into
    the formula?

    September is a 3 paycheck month.


    --
    jkh1978
    ------------------------------------------------------------------------
    jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    View this thread: http://www.excelforum.com/showthread...hreadid=397278


    Reply With QuoteRegister To Reply
  167. 09-06-2005, 05:05 PM #167
    Ragdyer
    Guest

    Re: Number of paychecks in a month?

    The formula that I suggested will count the paychecks in each month and
    display that count wherever you insert the formula.
    The formula is set to be copied to each column, and read the column date
    from the top row that column, and compare that date to the list of your
    paydays that I directed you to create, in an "out-of-the-way" location of
    your sheet.

    Would you care to try it before commenting that it doesn't do what you wish?
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------
    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Thanks for the reply... but thats not what I need. I need to know the
    > number of paychecks in a month.
    >
    > Some months have 5 fridays, so its unclear if its a month that i get 2
    > or 3 paychecks. What forumula would tell me this?
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  168. 09-06-2005, 05:05 PM #168
    Bob Phillips
    Guest

    Re: Number of paychecks in a month?

    Well I tried it and it tells me that info.

    You do not to setup the table the start with, but it's fine then.

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Thanks for the reply... but thats not what I need. I need to know the
    > number of paychecks in a month.
    >
    > Some months have 5 fridays, so its unclear if its a month that i get 2
    > or 3 paychecks. What forumula would tell me this?
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  169. 09-06-2005, 05:05 PM #169
    Sandy Mann
    Guest

    Re: Number of paychecks in a month?

    One way is to check is the Friday 28 days ahead of the 1st Friday in the
    month is still in the same month.

    Adapting a formula for the 1st Friday originally posted by Ron Rosenfeld
    almost exactly a year ago try:

    =IF(MONTH(DATE(YEAR(A1),MONTH(A1),1)+7-WEEKDAY(DATE(YEAR(A1),MONTH(A1),2))+28)=MONTH(A1),"3
    paychecks","2 paychecks")

    With any date in the target month in A1
    --
    HTH

    Sandy
    [email protected]
    Replace@mailinator with @tiscali.co.uk


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > In my spreadsheet, I have a column for each month, 8/1/05, 9/1/05,
    > etc...
    >
    > What I want to know is, how many paychecks will I receive each month?
    >
    > I get paid every other week on a Friday.
    >
    > My first attempt with help from reading other posts was to do this
    >
    > =(4+(DAY(D1-DAY(D1)+1-WEEKDAY(D1-DAY(D1+5))+35)>7))/2
    >
    > To get the number of fridays, divided by 2. But that returns 2.5 in
    > some months, and I dont know which of these 4 months each year gets me
    > the extra paycheck.
    >
    > Thanks for the help
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    > http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >



    Reply With QuoteRegister To Reply
  170. 09-06-2005, 05:05 PM #170
    RagDyer
    Guest

    Re: Number of paychecks in a month?

    Did you get it all sorted out, or are you still having a problem?
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sorry RagDyeR , I may have misundertstood what you meant.
    >
    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls.
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  171. 09-06-2005, 05:05 PM #171
    RagDyeR
    Guest

    Re: Number of paychecks in a month?

    Would you like me to send you my test sheet?

    If so, post back with your address in pseudo text so the spammers can't
    decipher it.
    --

    Regards,

    RD
    ----------------------------------------------------------------------------
    -------------------
    Please keep all correspondence within the Group, so all may benefit !
    ----------------------------------------------------------------------------
    -------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...

    I still don't have the answer I need.


    --
    jkh1978
    ------------------------------------------------------------------------
    jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    View this thread: http://www.excelforum.com/showthread...hreadid=397278


    Reply With QuoteRegister To Reply
  172. 09-06-2005, 05:05 PM #172
    Sandy Mann
    Guest

    Re: Number of paychecks in a month?

    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls

    That is because I failed to check if the first Friday was a pay day. With
    any pay Friday in D1 try:

    =IF((AND(MOD(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+2)-D1,14)=0,MONTH(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+2)+28)=MONTH(A1))),"3
    paychecks","2 paychecks")

    (Shamlessly stealing a formula for the 1st Friday form Daniel.M this time
    <g>)

    --
    HTH

    Sandy
    [email protected]
    Replace@mailinator with @tiscali.co.uk


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sorry RagDyeR , I may have misundertstood what you meant.
    >
    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls.
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    > http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  173. 09-06-2005, 05:05 PM #173
    RagDyer
    Guest

    Re: Number of paychecks in a month?

    Sample test sheet is on the way.
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sandy Mann, I tried yours but only got 2 for every month
    >
    > Please send a test spreadsheet to jkh1978 AT hotmail.com
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  174. 09-06-2005, 06:05 PM #174
    Bob Phillips
    Guest

    Re: Number of paychecks in a month?

    Nor do we. If you get paid in the final fri of a 4 fri month, you will only
    get 2 the following month even if it has 5 fri.

    It all depends upon where you start from, only you know that.

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > In my spreadsheet, I have a column for each month, 8/1/05, 9/1/05,
    > etc...
    >
    > What I want to know is, how many paychecks will I receive each month?
    >
    > I get paid every other week on a Friday.
    >
    > My first attempt with help from reading other posts was to do this
    >
    > =(4+(DAY(D1-DAY(D1)+1-WEEKDAY(D1-DAY(D1+5))+35)>7))/2
    >
    > To get the number of fridays, divided by 2. But that returns 2.5 in
    > some months, and I dont know which of these 4 months each year gets me
    > the extra paycheck.
    >
    > Thanks for the help
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  175. 09-06-2005, 06:05 PM #175
    RagDyeR
    Guest

    Re: Number of paychecks in a month?

    One way.

    You get paid every 14 days ... right?

    Put the date of your first paycheck in say Z1.
    In Z2 enter
    =Z1+14
    And copy down, giving you a list of paydays.

    Then enter this formula in the appropriate row in say Column A for January,
    and copy across for each month:

    =SUMPRODUCT(--(TEXT($Z$1:$Z$26,"mm-yy")=TEXT(A1,"mm-yy")))

    --

    HTH,

    RD
    =====================================================
    Please keep all correspondence within the Group, so all may benefit!
    =====================================================

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...

    That's part of what I'm stuggling with... how do I get that fact into
    the formula?

    September is a 3 paycheck month.


    --
    jkh1978
    ------------------------------------------------------------------------
    jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    View this thread: http://www.excelforum.com/showthread...hreadid=397278


    Reply With QuoteRegister To Reply
  176. 09-06-2005, 06:05 PM #176
    Ragdyer
    Guest

    Re: Number of paychecks in a month?

    The formula that I suggested will count the paychecks in each month and
    display that count wherever you insert the formula.
    The formula is set to be copied to each column, and read the column date
    from the top row that column, and compare that date to the list of your
    paydays that I directed you to create, in an "out-of-the-way" location of
    your sheet.

    Would you care to try it before commenting that it doesn't do what you wish?
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------
    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Thanks for the reply... but thats not what I need. I need to know the
    > number of paychecks in a month.
    >
    > Some months have 5 fridays, so its unclear if its a month that i get 2
    > or 3 paychecks. What forumula would tell me this?
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  177. 09-06-2005, 06:05 PM #177
    Bob Phillips
    Guest

    Re: Number of paychecks in a month?

    Well I tried it and it tells me that info.

    You do not to setup the table the start with, but it's fine then.

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Thanks for the reply... but thats not what I need. I need to know the
    > number of paychecks in a month.
    >
    > Some months have 5 fridays, so its unclear if its a month that i get 2
    > or 3 paychecks. What forumula would tell me this?
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  178. 09-06-2005, 06:05 PM #178
    Sandy Mann
    Guest

    Re: Number of paychecks in a month?

    One way is to check is the Friday 28 days ahead of the 1st Friday in the
    month is still in the same month.

    Adapting a formula for the 1st Friday originally posted by Ron Rosenfeld
    almost exactly a year ago try:

    =IF(MONTH(DATE(YEAR(A1),MONTH(A1),1)+7-WEEKDAY(DATE(YEAR(A1),MONTH(A1),2))+28)=MONTH(A1),"3
    paychecks","2 paychecks")

    With any date in the target month in A1
    --
    HTH

    Sandy
    [email protected]
    Replace@mailinator with @tiscali.co.uk


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > In my spreadsheet, I have a column for each month, 8/1/05, 9/1/05,
    > etc...
    >
    > What I want to know is, how many paychecks will I receive each month?
    >
    > I get paid every other week on a Friday.
    >
    > My first attempt with help from reading other posts was to do this
    >
    > =(4+(DAY(D1-DAY(D1)+1-WEEKDAY(D1-DAY(D1+5))+35)>7))/2
    >
    > To get the number of fridays, divided by 2. But that returns 2.5 in
    > some months, and I dont know which of these 4 months each year gets me
    > the extra paycheck.
    >
    > Thanks for the help
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    > http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >



    Reply With QuoteRegister To Reply
  179. 09-06-2005, 06:05 PM #179
    RagDyer
    Guest

    Re: Number of paychecks in a month?

    Did you get it all sorted out, or are you still having a problem?
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sorry RagDyeR , I may have misundertstood what you meant.
    >
    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls.
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  180. 09-06-2005, 06:05 PM #180
    RagDyeR
    Guest

    Re: Number of paychecks in a month?

    Would you like me to send you my test sheet?

    If so, post back with your address in pseudo text so the spammers can't
    decipher it.
    --

    Regards,

    RD
    ----------------------------------------------------------------------------
    -------------------
    Please keep all correspondence within the Group, so all may benefit !
    ----------------------------------------------------------------------------
    -------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...

    I still don't have the answer I need.


    --
    jkh1978
    ------------------------------------------------------------------------
    jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    View this thread: http://www.excelforum.com/showthread...hreadid=397278


    Reply With QuoteRegister To Reply
  181. 09-06-2005, 06:05 PM #181
    Sandy Mann
    Guest

    Re: Number of paychecks in a month?

    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls

    That is because I failed to check if the first Friday was a pay day. With
    any pay Friday in D1 try:

    =IF((AND(MOD(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+2)-D1,14)=0,MONTH(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+2)+28)=MONTH(A1))),"3
    paychecks","2 paychecks")

    (Shamlessly stealing a formula for the 1st Friday form Daniel.M this time
    <g>)

    --
    HTH

    Sandy
    [email protected]
    Replace@mailinator with @tiscali.co.uk


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sorry RagDyeR , I may have misundertstood what you meant.
    >
    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls.
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    > http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  182. 09-06-2005, 06:05 PM #182
    RagDyer
    Guest

    Re: Number of paychecks in a month?

    Sample test sheet is on the way.
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sandy Mann, I tried yours but only got 2 for every month
    >
    > Please send a test spreadsheet to jkh1978 AT hotmail.com
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  183. 09-06-2005, 07:05 PM #183
    Bob Phillips
    Guest

    Re: Number of paychecks in a month?

    Nor do we. If you get paid in the final fri of a 4 fri month, you will only
    get 2 the following month even if it has 5 fri.

    It all depends upon where you start from, only you know that.

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > In my spreadsheet, I have a column for each month, 8/1/05, 9/1/05,
    > etc...
    >
    > What I want to know is, how many paychecks will I receive each month?
    >
    > I get paid every other week on a Friday.
    >
    > My first attempt with help from reading other posts was to do this
    >
    > =(4+(DAY(D1-DAY(D1)+1-WEEKDAY(D1-DAY(D1+5))+35)>7))/2
    >
    > To get the number of fridays, divided by 2. But that returns 2.5 in
    > some months, and I dont know which of these 4 months each year gets me
    > the extra paycheck.
    >
    > Thanks for the help
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  184. 09-06-2005, 07:05 PM #184
    RagDyeR
    Guest

    Re: Number of paychecks in a month?

    One way.

    You get paid every 14 days ... right?

    Put the date of your first paycheck in say Z1.
    In Z2 enter
    =Z1+14
    And copy down, giving you a list of paydays.

    Then enter this formula in the appropriate row in say Column A for January,
    and copy across for each month:

    =SUMPRODUCT(--(TEXT($Z$1:$Z$26,"mm-yy")=TEXT(A1,"mm-yy")))

    --

    HTH,

    RD
    =====================================================
    Please keep all correspondence within the Group, so all may benefit!
    =====================================================

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...

    That's part of what I'm stuggling with... how do I get that fact into
    the formula?

    September is a 3 paycheck month.


    --
    jkh1978
    ------------------------------------------------------------------------
    jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    View this thread: http://www.excelforum.com/showthread...hreadid=397278


    Reply With QuoteRegister To Reply
  185. 09-06-2005, 07:05 PM #185
    Ragdyer
    Guest

    Re: Number of paychecks in a month?

    The formula that I suggested will count the paychecks in each month and
    display that count wherever you insert the formula.
    The formula is set to be copied to each column, and read the column date
    from the top row that column, and compare that date to the list of your
    paydays that I directed you to create, in an "out-of-the-way" location of
    your sheet.

    Would you care to try it before commenting that it doesn't do what you wish?
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------
    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Thanks for the reply... but thats not what I need. I need to know the
    > number of paychecks in a month.
    >
    > Some months have 5 fridays, so its unclear if its a month that i get 2
    > or 3 paychecks. What forumula would tell me this?
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  186. 09-06-2005, 07:05 PM #186
    Bob Phillips
    Guest

    Re: Number of paychecks in a month?

    Well I tried it and it tells me that info.

    You do not to setup the table the start with, but it's fine then.

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Thanks for the reply... but thats not what I need. I need to know the
    > number of paychecks in a month.
    >
    > Some months have 5 fridays, so its unclear if its a month that i get 2
    > or 3 paychecks. What forumula would tell me this?
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  187. 09-06-2005, 07:05 PM #187
    Sandy Mann
    Guest

    Re: Number of paychecks in a month?

    One way is to check is the Friday 28 days ahead of the 1st Friday in the
    month is still in the same month.

    Adapting a formula for the 1st Friday originally posted by Ron Rosenfeld
    almost exactly a year ago try:

    =IF(MONTH(DATE(YEAR(A1),MONTH(A1),1)+7-WEEKDAY(DATE(YEAR(A1),MONTH(A1),2))+28)=MONTH(A1),"3
    paychecks","2 paychecks")

    With any date in the target month in A1
    --
    HTH

    Sandy
    [email protected]
    Replace@mailinator with @tiscali.co.uk


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > In my spreadsheet, I have a column for each month, 8/1/05, 9/1/05,
    > etc...
    >
    > What I want to know is, how many paychecks will I receive each month?
    >
    > I get paid every other week on a Friday.
    >
    > My first attempt with help from reading other posts was to do this
    >
    > =(4+(DAY(D1-DAY(D1)+1-WEEKDAY(D1-DAY(D1+5))+35)>7))/2
    >
    > To get the number of fridays, divided by 2. But that returns 2.5 in
    > some months, and I dont know which of these 4 months each year gets me
    > the extra paycheck.
    >
    > Thanks for the help
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    > http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >



    Reply With QuoteRegister To Reply
  188. 09-06-2005, 07:05 PM #188
    RagDyer
    Guest

    Re: Number of paychecks in a month?

    Did you get it all sorted out, or are you still having a problem?
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sorry RagDyeR , I may have misundertstood what you meant.
    >
    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls.
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  189. 09-06-2005, 07:05 PM #189
    RagDyeR
    Guest

    Re: Number of paychecks in a month?

    Would you like me to send you my test sheet?

    If so, post back with your address in pseudo text so the spammers can't
    decipher it.
    --

    Regards,

    RD
    ----------------------------------------------------------------------------
    -------------------
    Please keep all correspondence within the Group, so all may benefit !
    ----------------------------------------------------------------------------
    -------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...

    I still don't have the answer I need.


    --
    jkh1978
    ------------------------------------------------------------------------
    jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    View this thread: http://www.excelforum.com/showthread...hreadid=397278


    Reply With QuoteRegister To Reply
  190. 09-06-2005, 07:05 PM #190
    Sandy Mann
    Guest

    Re: Number of paychecks in a month?

    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls

    That is because I failed to check if the first Friday was a pay day. With
    any pay Friday in D1 try:

    =IF((AND(MOD(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+2)-D1,14)=0,MONTH(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+2)+28)=MONTH(A1))),"3
    paychecks","2 paychecks")

    (Shamlessly stealing a formula for the 1st Friday form Daniel.M this time
    <g>)

    --
    HTH

    Sandy
    [email protected]
    Replace@mailinator with @tiscali.co.uk


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sorry RagDyeR , I may have misundertstood what you meant.
    >
    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls.
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    > http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  191. 09-06-2005, 07:05 PM #191
    RagDyer
    Guest

    Re: Number of paychecks in a month?

    Sample test sheet is on the way.
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sandy Mann, I tried yours but only got 2 for every month
    >
    > Please send a test spreadsheet to jkh1978 AT hotmail.com
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
  192. 09-06-2005, 09:05 PM #192
    RagDyeR
    Guest

    Re: Number of paychecks in a month?

    Would you like me to send you my test sheet?

    If so, post back with your address in pseudo text so the spammers can't
    decipher it.
    --

    Regards,

    RD
    ----------------------------------------------------------------------------
    -------------------
    Please keep all correspondence within the Group, so all may benefit !
    ----------------------------------------------------------------------------
    -------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...

    I still don't have the answer I need.


    --
    jkh1978
    ------------------------------------------------------------------------
    jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    View this thread: http://www.excelforum.com/showthread...hreadid=397278


    Reply With QuoteRegister To Reply
  193. 09-06-2005, 09:05 PM #193
    Sandy Mann
    Guest

    Re: Number of paychecks in a month?

    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls

    That is because I failed to check if the first Friday was a pay day. With
    any pay Friday in D1 try:

    =IF((AND(MOD(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+2)-D1,14)=0,MONTH(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+2)+28)=MONTH(A1))),"3
    paychecks","2 paychecks")

    (Shamlessly stealing a formula for the 1st Friday form Daniel.M this time
    <g>)

    --
    HTH

    Sandy
    [email protected]
    Replace@mailinator with @tiscali.co.uk


    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sorry RagDyeR , I may have misundertstood what you meant.
    >
    > The formula from Sandy Mann is not right... It tells me the months with
    > 3 weeks, not those two months where the extra paycheck falls.
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    > http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >


    Reply With QuoteRegister To Reply
  194. 09-06-2005, 09:05 PM #194
    RagDyer
    Guest

    Re: Number of paychecks in a month?

    Sample test sheet is on the way.
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------

    "jkh1978" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Sandy Mann, I tried yours but only got 2 for every month
    >
    > Please send a test spreadsheet to jkh1978 AT hotmail.com
    >
    >
    > --
    > jkh1978
    > ------------------------------------------------------------------------
    > jkh1978's Profile:
    http://www.excelforum.com/member.php...o&userid=26461
    > View this thread: http://www.excelforum.com/showthread...hreadid=397278
    >

    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1