No, please don't delete my message. I want it.
As you know, I was learning about WORKDAY( ). So, I changed my clock/calendar to 2008 to test the formula for leap year. Then I forgot to change it back. After a few days, I realized and then I changed it back to 2006.
If you have read my other post, you will be aware that I do my best in terms of etiquette. So, always ask first before anyone jumps to conclusion.
For the record, I DID NOT try to have my post on top by changing the date!!
I really like to have all my messages because I haven't recorded all into my database yet. Glad I caught your message. Please recover if you have deleted.
Epinn
"Don Guillett" wrote in message ...
Since your system date is wrong, I am taking the liberty of deleting your
message.
Some do this on purpose to make sure that their message is at the top. Did
you?
--
Don Guillett
SalesAid Software
"Epinn" wrote in message
...
Hi all,
Bob is more than a demonstrative teacher; he is understanding too. Yes, it
was a case of information overload. I thought I had never seen an equal
sign without "if" when I actually saw it quite a few times with SUMPRODUCT.
After staying away from this thread for a couple of days and coming back
fresh, I have a better grasp of the formulae.
=WORKDAY(DATE(YEAR(A1),MONTH(A1)+1,1),-1)
The above formula is so straight-forward that I can understand easily.
=EOMONTH(TODAY(),0)-(WEEKDAY(EOMONTH(TODAY(),0))={1,7})*({2,1})
I finally understand this with Bob's English interpretation plus realizing
the dash is a minus sign and the star is coercing and not a multiplication
sign. Same idea as SUMPRODUCT, no big deal. I didn't know I could use this
kind of format (array, coercing etc.) widely.
What a relief!
I don't understand Biff's shortest formula though.
=WORKDAY(EOMONTH(A1,0)-7,5)
"5" means there are five work days, right? Can't figure out minus 7? 7 is
Saturday or 7 means 7 days in a week. I don't want to torture my brain too
much now ......
I won't worry about the non-ATP formulae as I want to prevent a
short-circuit.
I lost faith in that web site as they had the wrong formula. Now, I am
switching to another web site with lots of goodies.
http://www.cpearson.com/excel/DateTimeWS.htm
C. Pearson is a popular name around here, right? Is he an MVP? He uses
non-ATP formulae. Not sure if they are identical to the ones suggested
here. Too lazy to check.
Is Biff a real name or is it an acronym like Epinn? Does Biff stand for
"big in functions/ formulae" by chance? ;)
Thank you for the input.
Epinn
"Bob Phillips" wrote in message
...
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