Excel - Need formula to calculate bi-monthly pay dates

Asked By JJ in LA on 25-Feb-10 05:58 PM
I need to create a list or table that returns all pay dates for the 15th and
last day of the month for an entire year.  The date must adjust forward when
the normal pay date falls on a weekend or holiday, e.g. the 15th is Sunday so
pay day is the 12th.  My attempts have resulted in excessive formulas and no
concrete answer.




T. Valko replied to JJ in LA on 25-Feb-10 07:24 PM
Do you need to account for holidays? This formula works but I do not account
for holidays although it'd be easy enough to do so. I am thinking some "date
wizard" (a person that is good at date formulas, that is not me!) can
probably come up with something more concise but this is a start!

A1 = the Jan 1st date of the year of interest.

A1 = 1/1/2010

Enter this formula in A2 and copy down to A25:

=IF(MOD(ROWS(A$2:A2),2),WORKDAY(DATE(YEAR(A$1),MONTH(A$1)+CEILING(ROWS(A$2:A2)/2,1)-1,1)-DAY(A$1)+16,-1),WORKDAY(EOMONTH(A1,0)-7,5))

If you are using a version of Excel prior to Excel 2007 then this formula
requires that the Analysis ToolPak add-in be installed.

If you enter the formula and get a #NAME? error look in Excel help for
either the WORKDAY or EOMONTH  functions. it will tell you how to fix the
problem.

--
Biff
Microsoft Excel MVP
Ron Rosenfeld replied to JJ in LA on 25-Feb-10 09:07 PM
It would be more convenient if you just expanded your request in the same
thread as you started, instead of starting a new one.

In any event, you can modify the technique I suggested before, to meet your
newly stated requirements, by entering:

=WORKDAY(DATE(Year_Ref,ROWS($1:1)/2+1,MOD(ROWS($1:1),2)*15+1),-1,Holidays)

in some cell and filling down to the end of the year (total of 48 rows).

Year_Ref is the year you are interested in, or a cell containing that year.

Holidays represents a range where you have listed the holidays for the year
specified by Year_Ref.

--ron
T. Valko replied to T. Valko on 25-Feb-10 09:48 PM
Minor tweak that saves a few keystrokes and eliminates one function call.

=IF(MOD(ROWS(A$2:A2),2),WORKDAY(DATE(YEAR(A$1),MONTH(A$1)+CEILING(ROWS(A$2:A2)/2,1)-1,1)-1+16,-1),WORKDAY(EOMONTH(A1,0)-7,5))

I like Ron's suggestion. Much more compact.

--
Biff
Microsoft Excel MVP
Ron Rosenfeld replied to Ron Rosenfeld on 26-Feb-10 10:15 PM
Just to emphasize one point, Year_Ref is just the year as a four digit year:
e.g. 2010.  It is NOT some date in the year  (e.g. it is NOT 1/1/2010)
--ron
Ron Rosenfeld replied to T. Valko on 26-Feb-10 10:15 PM
Thanks, Biff.
--ron