.... see HELP for the WORKDAY function ...
--
David Biddulph

"nbslarson" wrote in message
...
Oops. Spoke too soon. How do I rule out holidays (Labor day)?

"Ron Rosenfeld" wrote:

On Thu, 23 Jul 2009 06:52:02 -0700, nbslarson
wrote:

Our working period is divided into 2 sections a month: 1st thru 15th
and
16th thru last. We are paid on the 6th of the month and the 21st of the
month OR the closest workday to the end of the pay period.

Can you help me construct a formula to return this value?

Thank you for any help you can offer.

Further thoughts.

If your decision regarding the pay day is properly expressed as:

Pay day is 6 days after the end of the section; but if that day falls on
a
non-Workday, pay on the previous workday; then the formula is:

A1: contains the ending date for the section (e.g. 30 Sep 2009)

Payday:
=WORKDAY(A1+7,-1)

What this will return, in general, is the 6th and 21st of the month.
However,
if those days fall on a weekend (or holiday if you use the optional
holidays
argument for the WORKDAY function), then pay day will be on the preceding
WORKDAY (e.g. Friday).

If the formula returns a #NAME! error, see HELP for the WORKDAY function
to
correct this.

A1 must contain an Excel date. Substitute whatever cell reference you
have
that contains the date, or a formula to construct the date from other
information.
--ron