Results 1 to 2 of 2

1st and 15th pay period calculation

This is a discussion on 1st and 15th pay period calculation within the Excel Questions forums, part of the Question Forums category; I am working on a payroll schedule that needs to list the 1st and 15th pay periods (ie: 01/15/10, 01/31/10). ...

  1. #1
    New Member
    Join Date
    May 2010
    Posts
    1

    Question 1st and 15th pay period calculation

    I am working on a payroll schedule that needs to list the 1st and 15th pay periods (ie: 01/15/10, 01/31/10). I am trying to find a quick way to auto fill the page with these dates for the entire year. I am also need it to only fill weekdays.

    Example: May 15th is a Saturday so the actual fill date would be 05/14/10, the next pay date would be 05/31/10, the next 06/15/10, 06/30/10, 07/15/10, 07/30/10, and so on...and so on.

    I have done this before, but it has been so many years ago that I cannot remember how I did it. Please help!!

  2. #2
    MrExcel MVP DonkeyOte's Avatar
    Join Date
    Sep 2002
    Location
    Suffolk, UK
    Posts
    9,113

    Default Re: 1st and 15th pay period calculation

    Not clear which version of XL you're using but I would suggest you make use of WORKDAY function which prior to XL2007 requires activation of the Analysis ToolPak add-in via Tools -> Add-ins, at which point:

    Sheet3

     A
    12010
    215-Jan-10
    329-Jan-10
    415-Feb-10
    526-Feb-10
    615-Mar-10
    731-Mar-10
    815-Apr-10
    930-Apr-10
    1014-May-10
    1131-May-10
    1215-Jun-10
    1330-Jun-10
    1415-Jul-10
    1530-Jul-10
    1613-Aug-10
    1731-Aug-10
    1815-Sep-10
    1930-Sep-10
    2015-Oct-10
    2129-Oct-10
    2215-Nov-10
    2330-Nov-10
    2415-Dec-10
    2531-Dec-10

    Spreadsheet Formulas
    CellFormula
    A2=WORKDAY(DATE($A$1,1+INT(ROWS(A$2:A2)/2),IF(MOD(ROWS(A$2:A2),2),16,1)),-1)


    Excel tables to the web >> Excel Jeanie HTML 4

    The value in A1 is manual entry (year).

    WORKDAY has a further optional parameter to cater for public holidays (Mon-Fri) - see XL Help for more info.
    Does my a$$ look big in this picture ?

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com