That "love" and "grace" have *interesting* connotations!<vbg
--
Regards,
RD
---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Bob Phillips" wrote in message
...
Biff,
You are anal (said with love, humour, and grace :-))
Bob
"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