+ Reply to Thread
Results 1 to 8 of 8

Number of Pay Periods in Range with SumProduct

  1. #1
    Registered User
    Join Date
    12-29-2015
    Location
    Sunnyvale, CA
    MS-Off Ver
    Excel 2010
    Posts
    10

    Number of Pay Periods in Range with SumProduct

    Hi Folks,

    I am trying to calculate the number of pay periods falling in between two dates, based off a table of pay period dates and coverage start / end dates. Sample spreadsheet attached.

    =SUMPRODUCT(--(Payroll!$D$2:$D$27>='List of Enrolled EE''s'!B2)*(--(Payroll!$E$2:$E$27<='List of Enrolled EE''s'!C2)))

    This is my working formula. It's correct some of the time but not all. I can't just add +1 to it, as then my correct numbers are changed too.

    Sample Spreadsheet.xlsx

    Thoughts? Thanks so much for your help!
    Last edited by bgoodsell; 01-03-2016 at 09:17 PM.

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,937

    Re: Number of Pay Periods in Range with SumProduct

    I find no dates in Payroll!$E$2:$E$27 <= 1/2/2015. So the zero in D28 of ‘List of Enrolled EE's’ is correct. I haven’t checked the others.

    Edit I checked the rest of the cells marked 'should be' and they are correct, too.
    Last edited by FlameRetired; 12-30-2015 at 07:43 PM.
    Dave

  3. #3
    Registered User
    Join Date
    12-29-2015
    Location
    Sunnyvale, CA
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Number of Pay Periods in Range with SumProduct

    Thanks. Sorry - I realized my sample spreadsheet's formula was off by a column. It should have been referencing start and end dates for the pay periods but ended up referencing paycheck date instead.

    I need to correctly count the # of pay periods that occurred between a person's coverage effective date and coverage end date. If an employee has coverage during any portion of a payroll, they'll have a deduction taken from their paycheck.

    For ex. Morticia Addams with coverage from 1/1 - 1/2, there should be one pay period within which those dates fall (the pay period running 12/31/14 - 1/3/15. So it should return 1, not 0.

    Sample Spreadsheet2.xlsx

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,937

    Re: Number of Pay Periods in Range with SumProduct

    For ex. Morticia Addams with coverage from 1/1 - 1/2, there should be one pay period within which those dates fall (the pay period running 12/31/14 - 1/3/15. So it should return 1, not 0.
    Please disregard I found the answers to these.


    Should holidays and weekends be considered? 1/1/15 is a holiday and 1/3/15 is a Saturday.

    Also in the above you indicate pay period running 12/31/14 - 1/3/15. I don't know if it will matter or not but there is

    a pay period 12/21/14 - 1/3/15 but not 12/31/14 - 1/3/15.

    I assume one of these is a typo. Which one? If the period is supposed to be from 12/21 there is another holiday if it matters.

    I still cannot get all of the counts to agree with your expected numbers. I keep coming up with new disagreements, though. LOL
    Last edited by FlameRetired; 12-31-2015 at 01:10 AM.

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,937

    Re: Number of Pay Periods in Range with SumProduct

    Try this in D2 filled down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Edit You almost had it. The references just needed to be crossed.
    Last edited by FlameRetired; 12-31-2015 at 12:40 PM.

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,937

    Re: Number of Pay Periods in Range with SumProduct

    Also this works and does not require arrays.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    12-29-2015
    Location
    Sunnyvale, CA
    MS-Off Ver
    Excel 2010
    Posts
    10

    Thumbs up Re: Number of Pay Periods in Range with SumProduct

    Thank you so much! This did the trick perfectly. I really appreciate it.

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,937

    Re: Number of Pay Periods in Range with SumProduct

    You are welcome. Thank you for the feedback.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to calculate number of pay periods??
    By Rheanna in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-10-2015, 01:12 PM
  2. Replies: 4
    Last Post: 09-01-2014, 05:25 AM
  3. [SOLVED] SUMPRODUCT Critera - If range values start with number
    By daffodil11 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-04-2014, 01:03 PM
  4. Counting the number of parallel periods
    By flossdraad in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-21-2013, 03:23 PM
  5. [SOLVED] SUMPRODUCT from a DATE RANGE based upon a product and static number
    By mmayna03 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-14-2013, 10:33 AM
  6. [SOLVED] Calculating number of periods when payment amount changes
    By Dan Patrick in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM
  7. Number of semi-monthly periods between 2 dates
    By sforr in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1