Need formula to calculate bi-monthly pay dates
 

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.

Need formula to calculate bi-monthly pay dates
 

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),MON TH(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.

--
Biff
Microsoft Excel MVP


"JJ in LA" wrote in message
...
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.

Need formula to calculate bi-monthly pay dates
 

On Thu, 25 Feb 2010 14:58:08 -0800, JJ in LA
wrote:

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

Need formula to calculate bi-monthly pay dates
 

Minor tweak that saves a few keystrokes and eliminates one function call.

=IF(MOD(ROWS(A$2:A2),2),WORKDAY(DATE(YEAR(A$1),MON TH(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


"T. Valko" wrote in message
...
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),MON TH(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.

--
Biff
Microsoft Excel MVP


"JJ in LA" wrote in message
...
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.

Need formula to calculate bi-monthly pay dates
 

On Thu, 25 Feb 2010 21:07:04 -0500, Ron Rosenfeld
wrote:

On Thu, 25 Feb 2010 14:58:08 -0800, JJ in LA
wrote:

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

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

Need formula to calculate bi-monthly pay dates
 

On Thu, 25 Feb 2010 21:48:04 -0500, "T. Valko" wrote:

I like Ron's suggestion. Much more compac

Thanks, Biff.
--ron