Need formula to calculate bi-monthly pay dates

[JJ in LA]

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]

Do you need to account for holidays? This formula works but I don't account
for holidays although it'd be easy enough to do so. I'm thinking some "date
wizard" (a person that is good at date formulas, that's 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're 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'll tell you how to fix the
problem.

 

[Ron Rosenfeld]

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.

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]

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.

 

[Ron Rosenfeld]

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

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]

I like Ron's suggestion. Much more compac

Thanks, Biff.
--ron