Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old August 29th 19, 12:00 AM
Junior Member
 
First recorded activity by ExcelBanter: Aug 2019
Posts: 1
Unhappy Calculate the number of pay periods in a given span

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

  #2   Report Post  
Old August 29th 19, 02:22 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,713
Default Calculate the number of pay periods in a given span

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
  #3   Report Post  
Old August 29th 19, 02:27 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,713
Default Calculate the number of pay periods in a given span

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
  #4   Report Post  
Old August 29th 19, 02:29 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,713
Default Calculate the number of pay periods in a given span

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculate pay periods between two dates if paid on 1st and 15th? janplan Excel Discussion (Misc queries) 2 August 13th 08 03:48 AM
calculate remaining pay periods maijiuli Excel Worksheet Functions 5 January 24th 08 10:32 PM
HOW to calculate the interest paid between 2 custom periods? TiDz Excel Worksheet Functions 1 June 30th 06 12:46 PM
calculate time periods umba-sr Excel Worksheet Functions 1 February 21st 06 02:13 PM
How do I calculate rate when pmt is made at end of periods hsummer Excel Worksheet Functions 1 August 7th 05 10:29 PM


All times are GMT +1. The time now is 01:59 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017