I gave an alternate solution non-ATP earlier

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

and non-array

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Biff" wrote in message
...
What happens if you don't want to or can't use ATP functions? Then it gets
much more complicated!

I'm sure there is a better way to do this but this is the first thing that
came to my mind. This is an array formula and it also demonstrates the 0th
day of the month that I mentioned in the "Weekday" thread:


=MAX(IF(WEEKDAY(ROW(INDIRECT(DATE(YEAR(A1),MONTH(A 1),1)&":"&DATE(YEAR(A1),MO
NTH(A1)+1,0))),2)<6,ROW(INDIRECT(DATE(YEAR(A1),MON TH(A1),1)&":"&DATE(YEAR(A1
),MONTH(A1)+1,0)))))

Biff

"Biff" wrote in message
...
Shorter still: (ATP required)

=WORKDAY(EOMONTH(A1,0)-7,5)

Biff

"Bob Phillips" wrote in message
...
Hi Epinn,

I wonder what delights you will bring up here <g

The EOMONTH formula you give below is wrong, it should be


=IF(WEEKDAY(EOMONTH(TODAY(),0))=7,EOMONTH(TODAY(), 0)-1,IF(WEEKDAY(EOMONTH(TO
DAY(),0))=1,EOMONTH(TODAY(),0)-2,EOMONTH(TODAY(),0)))

which can be shortened to


=EOMONTH(TODAY(),0)-IF(WEEKDAY(EOMONTH(TODAY(),0))=7,1,IF(WEEKDAY(EOMO NTH(TO
DAY(),0))=1,2,0))

which I think helps see what is happening, or even shorter at

=EOMONTH(TODAY(),0)-(WEEKDAY(EOMONTH(TODAY(),0))={1,7})*({2,1})

As you say you need ATP for WORKDAY to function, but you probably have
it
installed, just not loaded. Goto ToolsAdd-Ins..., and check Analysis
Toolpak.

"Epinn" wrote in message
...
Bob,

Welcome to my new thread.

Bob's formula for the last work day of the current month (brought over
from
another thread WEEKDAY is
this:-

=WORKDAY(DATE(YEAR(A1),MONTH(A1)+1,1),-1)

Looks like this is similar to the second formula below which I like.

I was "intimidated" by the first one below. It was almost three lines
on
my
screeen. If I don't need to grasp EOMONTH(), I prefer not. I have to
have
ATP (Analysis Toolpak) to use WORKDAY(), right? My system told me
#N/A!,
so
I have to install it.


************************************************** **************************
*******
a.. Last workday of the current month:

=IF(WEEKDAY(EOMONTH(TODAY(),0))=6,EOMONTH(TODAY(), 0)-1,IF(WEEKDAY(EOMONTH(TO
DAY(),0))=1,EOMONTH(TODAY(),0)-2,EOMONTH(TODAY(),0)))

or

=WORKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1),-1,Holidays)

Source:

http://www.officearticles.com/excel/...soft_excel.htm

If link broken try this ...... http://tinyurl.com/mrzcc


************************************************** **************************

I will stay tuned.

Epinn