• Calculate the number of pay periods in a given span

    From ParalegalMP@21:1/5 to All on Thu Aug 29 00:00:13 2019
    I am trying to determine potential liability in an employment case. For
    each pay period in which work was performed, each employee is due a
    penalty amount. Even if they only worked a few days, they might have
    performed work in two different pay periods, so my formula to count how
    many 14 day periods there are in each employee's term of employment is
    not accurate enough.

    For each employee I need to determine the number of penalties they are
    owed, thus I need to know how many pay periods in which they performed
    work.

    I have each employee's hire date (Cell D4) and termination date (Cell
    E4).

    The pay period interval is 14 days (Sunday of Week One to Saturday of
    Week Two).

    The start of the penalty period is 11/9/14, which is the first day of
    the first pay period, and has a date serial number of 41952.

    Can a formula be written to yield the number of pay period end dates in
    a given span of time?

    Thanks in advance for any help.

    MP




    --
    ParalegalMP

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Claus Busch@21:1/5 to All on Thu Aug 29 03:22:24 2019
    Hi,

    Am Thu, 29 Aug 2019 00:00:13 +0100 schrieb ParalegalMP:

    I am trying to determine potential liability in an employment case. For
    each pay period in which work was performed, each employee is due a
    penalty amount. Even if they only worked a few days, they might have performed work in two different pay periods, so my formula to count how
    many 14 day periods there are in each employee's term of employment is
    not accurate enough.

    For each employee I need to determine the number of penalties they are
    owed, thus I need to know how many pay periods in which they performed
    work.

    I have each employee's hire date (Cell D4) and termination date (Cell
    E4).

    The pay period interval is 14 days (Sunday of Week One to Saturday of
    Week Two).

    The start of the penalty period is 11/9/14, which is the first day of
    the first pay period, and has a date serial number of 41952.

    Can a formula be written to yield the number of pay period end dates in
    a given span of time?

    try:
    =(MOD(D4-7-1,7)+E4-D4-2)/14


    Regards
    Claus B.
    --
    Windows10
    Office 2016

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Claus Busch@21:1/5 to All on Thu Aug 29 03:27:16 2019
    Hi again,

    Am Thu, 29 Aug 2019 03:22:24 +0200 schrieb Claus Busch:

    =(MOD(D4-7-1,7)+E4-D4-2)/14

    sorry, typo.
    Try:
    =(MOD(D4-1-1,7)+E4-D4-2)/14


    Regards
    Claus B.
    --
    Windows10
    Office 2016

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Claus Busch@21:1/5 to All on Thu Aug 29 03:29:39 2019
    Hi,

    Am Thu, 29 Aug 2019 03:27:16 +0200 schrieb Claus Busch:

    sorry, typo.
    Try:
    =(MOD(D4-1-1,7)+E4-D4-2)/14

    or try:
    =SUMPRODUCT((WEEKDAY(ROW(INDIRECT(D4&":"&E4)),2)=7)*1)/2


    Regards
    Claus B.
    --
    Windows10
    Office 2016

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)