I love his work, and I gracefully acknowledge his skill and expertise <vbg
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
"Ragdyer" wrote in message
...
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