(looking at that humongoid formula:) Holy ****!!!


"DOR" wrote in message
ups.com...
Biff,

(I posted this yesterday on Excel Forum, but it doesn't seem to have
come across to Google ... so this may be a duplicate)

The possibility of the last weekday of the month, or by the 15th, being
on a holiday also occurred to me, as I looked at your neat use of
EOMONTH and WORKDAY, but I didn't follow up. Your suggestion to use the
Holidays parameter is excellent, but should possibly go a little
further. You need to modify the second argument of the WORKDAY function
to add not 5, but the number of workdays that exist between EOM (or
15th) and EOM-6 by using the NETWORKDAYS function (6 rather than 7
because the NETWORKDAYS function is inclusive), in addition to using
the Holidays argument (in both the NETWORKDAYS and WORKDAY functions).
This produces a very cumbersome formula as follows:

=IF(DAY(A1)<=15,WORKDAY(EOMONTH(A1,0)-7,NETWORKDAYS(EOMONTH(A1,0)-6,EOMONTH(A1,0),Holidays),Holidays),WORKDAY(DATE(Y
EAR(A1),MONTH(A1)+1,15)-7,NETWORKDAYS(DATE(YEAR(A1),MONTH(A1)+1,15)-6,DATE(YEAR(A1),MONTH(A1)+1,15),Holidays),Holidays
))


However, it appears to work. Nevertheless, I'd prefer to break it up
into its constituent parts. What a nightmare for others to decode!

No doubt, you or someone else will provide a more elegant solution. I
still like your neat use of EOM-7+5 workdays ...

DOR

Biff wrote:
You still need to enter the first date in A1:

Enter in B1:

=IF(DAY(A1)<=15,WORKDAY(EOMONTH(A1,0)-7,5),WORKDAY(DATE(YEAR(A1),MONTH(A1)+1,15)-7,5))

Copy across, format as DATE, requires the ATP be installed.

Now, let me throw a monkey wrench into the mix that you may not have
considered!

What about paydays falling on holidays?

Luckily, the WORKDAY function can account for this. It can accept a third
argument that is a list of holiday dates.

You would create a list of the holiday dates and include a reference to
that
list as a third argument:

WORKDAY(arg_1,arg_2,H1:H10)

Biff

"Yeah" wrote in message
news:ppKGg.1078$xk3.707@dukeread07...
I feel like such a turdburglar! The part about the 15th is a little off,
too. So BOTH dates have to be formulas.

The paydays are supposed to be:
1) a) The 15th of every month, or, if it falls on a weekend, b) the
preceding weekday before the 15th
2) The last weekday of the month

"DOR" wrote in message
ups.com...
Try this - in A1 put your first legitimate date, such as 12/30/05,
then
in A2 enter:

=DATE(YEAR(A1),1+MONTH(A1),IF(DAY(A1)=15,0,15))-IF(DAY(A1)<15,0,MAX(WEEKDAY(DATE(YEAR(A1),1+MONTH (A1),0),2)-5,0))

and drag across. It's not very elegant, but it appears to work, and
it
crosses years ok.

HTH

Declan

Yeah wrote:
What a goofy subject, I know. For my monthly budget, I want to output
the
date in every column, two weeks apart. These represent paydays.
However,
paydays are on the 15th and the last weekday of every month.

(For example: 8/31, 9/15, 9/29, 10/15, 10/31...)

Obviously, these dates won't all be 14 days apart. Is there any way
to
tell
Excel to output the date in the above manner?