|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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(TODAY(),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/date_formulas_and_functions_in_microsoft_excel.htm
If link broken try this ...... http://tinyurl.com/mrzcc
****************************************************************************
I will stay tuned.
Epinn
|
|
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(EOMONTH(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 Tools>Add-Ins..., and check Analysis
Toolpak.
"Epinn" <someone[ at ]example.com.NO_SPAM> wrote in message
news:ue9jFyB1GHA.4748[ at ]TK2MSFTNGP04.phx.gbl...
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/date_formulas_and_functions_in_microsoft_excel.htm
If link broken try this ...... http://tinyurl.com/mrzcc
****************************************************************************
I will stay tuned.
Epinn
|
|
Bob,
Sorry, I disappointed you. <g> I am glad that I didn't spend hours trying to analyze what they put on the web site. May be the info there is not so reliable after all??
Yes, I agree that I only need to load. I did it once for another function and that was what I had in mind even though I said "installed." I should get some sleep.
Wow! You can shrink the formula to half!! However, I don't have a clue how to decipher it. Regarding the last (third) formula, this is the first time I see an equal sign in a formula without "if" present. I don't know if that little dash means minus and I don't understand the array constants. But I don't want to take up your time explaining it to me as I prefer you help me on something else that may need more of my attention in the future. You know, I don't feel like "bugging" you all day long. I'll just keep this formula in my bag for now.
Cheers,
Epinn
"Bob Phillips" <bob.NGs[ at ]somewhere.com> wrote in message news:uAqEdlC1GHA.4796[ at ]TK2MSFTNGP03.phx.gbl...
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(EOMONTH(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 Tools>Add-Ins..., and check Analysis
Toolpak.
"Epinn" <someone[ at ]example.com.NO_SPAM> wrote in message
news:ue9jFyB1GHA.4748[ at ]TK2MSFTNGP04.phx.gbl...
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/date_formulas_and_functions_in_microsoft_excel.htm
If link broken try this ...... http://tinyurl.com/mrzcc
****************************************************************************
I will stay tuned.
Epinn
|
|
"Epinn" <someone[ at ]example.com.NO_SPAM> wrote in message
news:uwL4nRD1GHA.4632[ at ]TK2MSFTNGP03.phx.gbl...
Bob,
[Quoted Text] > Sorry, I disappointed you. <g> I am glad that I didn't spend hours
> trying to analyze what they put on the web site. May be the info there
> is not so reliable after all??
The problem was that it was testing the last day for Friday or Sunday, it
should be testing for Saturday or Sunday.
> Wow! You can shrink the formula to half!! However, I don't have a
> clue how to decipher it. Regarding the last (third) formula, this is the
> first time I see an equal sign in a formula without "if" present.
I doubt that Epinn, it is just testing a condition. I think you will have
seen that many times in SUMPRODUCT
=SUMPRODUCT((rng1="A")*(rng2="B"))
so as before it is just getting a TRUE/FALSE result which is being coerced
by the * operator (again, just like you have seen in SUMPRODUCT). The
different thing here is I test against two value {1,7} and then using two
multipliers {2,1} which will get a value depending upon the day of the week
that the last day falls on.
> I don't know if that little dash means minus and I don't understand the
> array constants. But I don't want to take up your time explaining it to
> me as I prefer you help me on something else that may need more of
> my attention in the future. You know, I don't feel like "bugging" you all
> day long. I'll just keep this formula in my bag for now.
I fear you are getting information overload now mate <g>. That dash
certainly does mean minus, The formula just calculates the last date of the
month, then calculates if that last day is a Saturday or Sunday and
calculates the number of days to subtract if so, and subtracts them
=Get_end_of_month_date - adjustment
where
adjustment = If(day_of_end_of_month_date = Sat or Sun, return 1 or 2, else
return 0)
|
|
Shorter still: (ATP required)
=WORKDAY(EOMONTH(A1,0)-7,5)
Biff
"Bob Phillips" <bob.NGs[ at ]somewhere.com> wrote in message
news:uAqEdlC1GHA.4796[ at ]TK2MSFTNGP03.phx.gbl...
[Quoted Text] > 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(EOMONTH(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 Tools>Add-Ins..., and check Analysis > Toolpak. > > "Epinn" <someone[ at ]example.com.NO_SPAM> wrote in message > news:ue9jFyB1GHA.4748[ at ]TK2MSFTNGP04.phx.gbl... > 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/date_formulas_and_functions_in_microsoft_excel.htm> > If link broken try this ...... http://tinyurl.com/mrzcc> > **************************************************************************** > > I will stay tuned. > > Epinn > > > > > > >
|
|
Biff,
You are anal (said with love, humour, and grace :-))
Bob
"Biff" <biffinpitt[ at ]comcast.net> wrote in message
news:uO8anLE1GHA.4816[ at ]TK2MSFTNGP06.phx.gbl...
[Quoted Text] > Shorter still: (ATP required)
>
> =WORKDAY(EOMONTH(A1,0)-7,5)
>
> Biff
>
> "Bob Phillips" <bob.NGs[ at ]somewhere.com> wrote in message
> news:uAqEdlC1GHA.4796[ at ]TK2MSFTNGP03.phx.gbl...
> > 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(EOMONTH(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 Tools>Add-Ins..., and check Analysis
> > Toolpak.
> >
> > "Epinn" <someone[ at ]example.com.NO_SPAM> wrote in message
> > news:ue9jFyB1GHA.4748[ at ]TK2MSFTNGP04.phx.gbl...
> > 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/date_formulas_and_functions_in_microsoft_excel.htm
> >
> > If link broken try this ...... http://tinyurl.com/mrzcc
> >
> >
****************************************************************************
> >
> > I will stay tuned.
> >
> > Epinn
> >
> >
> >
> >
> >
> >
> >
>
>
|
|
That "love" and "grace" have *interesting* connotations!<vbg>
--
Regards,
RD
---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Bob Phillips" <bob.NGs[ at ]somewhere.com> wrote in message
news:%23s8yJJF1GHA.4392[ at ]TK2MSFTNGP04.phx.gbl...
[Quoted Text] > Biff,
>
> You are anal (said with love, humour, and grace :-))
>
> Bob
>
> "Biff" <biffinpitt[ at ]comcast.net> wrote in message
> news:uO8anLE1GHA.4816[ at ]TK2MSFTNGP06.phx.gbl...
> > Shorter still: (ATP required)
> >
> > =WORKDAY(EOMONTH(A1,0)-7,5)
> >
> > Biff
> >
> > "Bob Phillips" <bob.NGs[ at ]somewhere.com> wrote in message
> > news:uAqEdlC1GHA.4796[ at ]TK2MSFTNGP03.phx.gbl...
> > > 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(EOMONTH(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 Tools>Add-Ins..., and check Analysis
> > > Toolpak.
> > >
> > > "Epinn" <someone[ at ]example.com.NO_SPAM> wrote in message
> > > news:ue9jFyB1GHA.4748[ at ]TK2MSFTNGP04.phx.gbl...
> > > 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/date_formulas_and_functions_in_microsoft_excel.htm
> > >
> > > If link broken try this ...... http://tinyurl.com/mrzcc
> > >
> > >
>
****************************************************************************
> > >
> > > I will stay tuned.
> > >
> > > Epinn
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> >
> >
>
>
|
|
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(A1),1)&":"&DATE(YEAR(A1),MONTH(A1)+1,0))),2)<6,ROW(INDIRECT(DATE(YEAR(A1),MONTH(A1),1)&":"&DATE(YEAR(A1),MONTH(A1)+1,0)))))
Biff
"Biff" <biffinpitt[ at ]comcast.net> wrote in message
news:uO8anLE1GHA.4816[ at ]TK2MSFTNGP06.phx.gbl...
[Quoted Text] > Shorter still: (ATP required) > > =WORKDAY(EOMONTH(A1,0)-7,5) > > Biff > > "Bob Phillips" <bob.NGs[ at ]somewhere.com> wrote in message > news:uAqEdlC1GHA.4796[ at ]TK2MSFTNGP03.phx.gbl... >> 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(EOMONTH(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 Tools>Add-Ins..., and check Analysis >> Toolpak. >> >> "Epinn" <someone[ at ]example.com.NO_SPAM> wrote in message >> news:ue9jFyB1GHA.4748[ at ]TK2MSFTNGP04.phx.gbl... >> 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/date_formulas_and_functions_in_microsoft_excel.htm>> >> If link broken try this ...... http://tinyurl.com/mrzcc>> >> **************************************************************************** >> >> I will stay tuned. >> >> Epinn >> >> >> >> >> >> >> > >
|
|
|
[Quoted Text] > Biff,
> You are anal ....
Yes, I know. I think I have OCD (perfectionist). Just wait 'til I get this
stuff figured out!
Biff
"Bob Phillips" <bob.NGs[ at ]somewhere.com> wrote in message
news:%23s8yJJF1GHA.4392[ at ]TK2MSFTNGP04.phx.gbl...
> Biff,
>
> You are anal (said with love, humour, and grace :-))
>
> Bob
>
> "Biff" <biffinpitt[ at ]comcast.net> wrote in message
> news:uO8anLE1GHA.4816[ at ]TK2MSFTNGP06.phx.gbl...
>> Shorter still: (ATP required)
>>
>> =WORKDAY(EOMONTH(A1,0)-7,5)
>>
>> Biff
>>
>> "Bob Phillips" <bob.NGs[ at ]somewhere.com> wrote in message
>> news:uAqEdlC1GHA.4796[ at ]TK2MSFTNGP03.phx.gbl...
>> > 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(EOMONTH(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 Tools>Add-Ins..., and check Analysis
>> > Toolpak.
>> >
>> > "Epinn" <someone[ at ]example.com.NO_SPAM> wrote in message
>> > news:ue9jFyB1GHA.4748[ at ]TK2MSFTNGP04.phx.gbl...
>> > 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/date_formulas_and_functions_in_microsoft_excel.htm
>> >
>> > If link broken try this ...... http://tinyurl.com/mrzcc
>> >
>> >
> ****************************************************************************
>> >
>> > I will stay tuned.
>> >
>> > Epinn
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>>
>>
>
>
|
|
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" <biffinpitt[ at ]comcast.net> wrote in message
news:uMEwFRF1GHA.1256[ at ]TK2MSFTNGP04.phx.gbl...
[Quoted Text] > 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(A1),1)&":"&DATE(YEAR(A1),MO
NTH(A1)+1,0))),2)<6,ROW(INDIRECT(DATE(YEAR(A1),MONTH(A1),1)&":"&DATE(YEAR(A1
),MONTH(A1)+1,0)))))
>
> Biff
>
> "Biff" <biffinpitt[ at ]comcast.net> wrote in message
> news:uO8anLE1GHA.4816[ at ]TK2MSFTNGP06.phx.gbl...
> > Shorter still: (ATP required)
> >
> > =WORKDAY(EOMONTH(A1,0)-7,5)
> >
> > Biff
> >
> > "Bob Phillips" <bob.NGs[ at ]somewhere.com> wrote in message
> > news:uAqEdlC1GHA.4796[ at ]TK2MSFTNGP03.phx.gbl...
> >> 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(EOMONTH(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 Tools>Add-Ins..., and check Analysis
> >> Toolpak.
> >>
> >> "Epinn" <someone[ at ]example.com.NO_SPAM> wrote in message
> >> news:ue9jFyB1GHA.4748[ at ]TK2MSFTNGP04.phx.gbl...
> >> 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/date_formulas_and_functions_in_microsoft_excel.htm
> >>
> >> If link broken try this ...... http://tinyurl.com/mrzcc
> >>
> >>
****************************************************************************
> >>
> >> I will stay tuned.
> >>
> >> Epinn
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >
> >
>
>
|
|
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" <RagDyer[ at ]cutoutmsn.com> wrote in message
news:OA3hdMF1GHA.4176[ at ]TK2MSFTNGP06.phx.gbl...
[Quoted Text] > That "love" and "grace" have *interesting* connotations!<vbg>
> --
> Regards,
>
> RD
>
> --------------------------------------------------------------------------
-
> Please keep all correspondence within the NewsGroup, so all may benefit !
> --------------------------------------------------------------------------
-
> "Bob Phillips" <bob.NGs[ at ]somewhere.com> wrote in message
> news:%23s8yJJF1GHA.4392[ at ]TK2MSFTNGP04.phx.gbl...
> > Biff,
> >
> > You are anal (said with love, humour, and grace :-))
> >
> > Bob
> >
> > "Biff" <biffinpitt[ at ]comcast.net> wrote in message
> > news:uO8anLE1GHA.4816[ at ]TK2MSFTNGP06.phx.gbl...
> > > Shorter still: (ATP required)
> > >
> > > =WORKDAY(EOMONTH(A1,0)-7,5)
> > >
> > > Biff
> > >
> > > "Bob Phillips" <bob.NGs[ at ]somewhere.com> wrote in message
> > > news:uAqEdlC1GHA.4796[ at ]TK2MSFTNGP03.phx.gbl...
> > > > 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(EOMONTH(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 Tools>Add-Ins..., and check
Analysis
> > > > Toolpak.
> > > >
> > > > "Epinn" <someone[ at ]example.com.NO_SPAM> wrote in message
> > > > news:ue9jFyB1GHA.4748[ at ]TK2MSFTNGP04.phx.gbl...
> > > > 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/date_formulas_and_functions_in_microsoft_excel.htm
> > > >
> > > > If link broken try this ...... http://tinyurl.com/mrzcc
> > > >
> > > >
> >
>
****************************************************************************
> > > >
> > > > I will stay tuned.
> > > >
> > > > Epinn
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
|
|
Ahhh! ... the verbal agility of the British.<g>
--
Regards,
RD
---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Bob Phillips" <bob.NGs[ at ]somewhere.com> wrote in message
news:%23iF%23EOM1GHA.4108[ at ]TK2MSFTNGP04.phx.gbl...
[Quoted Text] > 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" <RagDyer[ at ]cutoutmsn.com> wrote in message
> news:OA3hdMF1GHA.4176[ at ]TK2MSFTNGP06.phx.gbl...
> > That "love" and "grace" have *interesting* connotations!<vbg>
> > --
> > Regards,
> >
> > RD
> >
>
> --------------------------------------------------------------------------
> -
> > Please keep all correspondence within the NewsGroup, so all may benefit
!
>
> --------------------------------------------------------------------------
> -
> > "Bob Phillips" <bob.NGs[ at ]somewhere.com> wrote in message
> > news:%23s8yJJF1GHA.4392[ at ]TK2MSFTNGP04.phx.gbl...
> > > Biff,
> > >
> > > You are anal (said with love, humour, and grace :-))
> > >
> > > Bob
> > >
> > > "Biff" <biffinpitt[ at ]comcast.net> wrote in message
> > > news:uO8anLE1GHA.4816[ at ]TK2MSFTNGP06.phx.gbl...
> > > > Shorter still: (ATP required)
> > > >
> > > > =WORKDAY(EOMONTH(A1,0)-7,5)
> > > >
> > > > Biff
> > > >
> > > > "Bob Phillips" <bob.NGs[ at ]somewhere.com> wrote in message
> > > > news:uAqEdlC1GHA.4796[ at ]TK2MSFTNGP03.phx.gbl...
> > > > > 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(EOMONTH(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 Tools>Add-Ins..., and check
> Analysis
> > > > > Toolpak.
> > > > >
> > > > > "Epinn" <someone[ at ]example.com.NO_SPAM> wrote in message
> > > > > news:ue9jFyB1GHA.4748[ at ]TK2MSFTNGP04.phx.gbl...
> > > > > 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/date_formulas_and_functions_in_microsoft_excel.htm
> > > > >
> > > > > If link broken try this ...... http://tinyurl.com/mrzcc
> > > > >
> > > > >
> > >
> >
>
****************************************************************************
> > > > >
> > > > > I will stay tuned.
> > > > >
> > > > > Epinn
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
>
>
|
|
"Epinn" <someone[ at ]example.com.NO_SPAM> wrote in message
news:uyZNV8s1GHA.4752[ at ]TK2MSFTNGP05.phx.gbl...
[Quoted Text] > 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 ......
It.s nothing to do with Saturday. What is happening is that the formula
first calculates the end of monthe date, and then takes 7 off of that to go
back into the month. Thus, if the last day is a Saturday, it will take you
back to the previous Saturday, Tuesday goes to Tuesday. WORKDAY is then
invoked with that date + 5 days which MUST take you to the last working days
(i.e 5 on Sat is Fri, 5 on Tue is Tue).
> I won't worry about the non-ATP formulae as I want to prevent a
short-circuit.
Chicken <vbg>
> 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.
Great site, great guy. He is indeed an MVP.
> Is Biff a real name or is it an acronym like Epinn? >
> Does Biff stand for "big in functions/ formulae" by chance? ;)
Could be an NG handle, I must admit I have always assumed that it is. But if
you recall, in Back to The Future, Marty's adversary is called Biff Tannen,
so it could be real.
|
|
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
dguillett1[ at ]austin.rr.com
"Epinn" <someone[ at ]example.com.NO_SPAM> wrote in message
news:uyZNV8s1GHA.4752[ at ]TK2MSFTNGP05.phx.gbl...
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" <bob.NGs[ at ]somewhere.com> wrote in message
news:uTFQrNM1GHA.2196[ at ]TK2MSFTNGP06.phx.gbl...
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" <biffinpitt[ at ]comcast.net> wrote in message
news:uMEwFRF1GHA.1256[ at ]TK2MSFTNGP04.phx.gbl...
[Quoted Text] > 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(A1),1)&":"&DATE(YEAR(A1),MO
NTH(A1)+1,0))),2)<6,ROW(INDIRECT(DATE(YEAR(A1),MONTH(A1),1)&":"&DATE(YEAR(A1
),MONTH(A1)+1,0)))))
>
> Biff
>
> "Biff" <biffinpitt[ at ]comcast.net> wrote in message
> news:uO8anLE1GHA.4816[ at ]TK2MSFTNGP06.phx.gbl...
> > Shorter still: (ATP required)
> >
> > =WORKDAY(EOMONTH(A1,0)-7,5)
> >
> > Biff
> >
> > "Bob Phillips" <bob.NGs[ at ]somewhere.com> wrote in message
> > news:uAqEdlC1GHA.4796[ at ]TK2MSFTNGP03.phx.gbl...
> >> 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(EOMONTH(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 Tools>Add-Ins..., and check Analysis
> >> Toolpak.
> >>
> >> "Epinn" <someone[ at ]example.com.NO_SPAM> wrote in message
> >> news:ue9jFyB1GHA.4748[ at ]TK2MSFTNGP04.phx.gbl...
> >> 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/date_formulas_and_functions_in_microsoft_excel.htm
> >>
> >> If link broken try this ...... http://tinyurl.com/mrzcc
> >>
> >>
****************************************************************************
> >>
> >> I will stay tuned.
> >>
> >> Epinn
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >
> >
>
>
|
|
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" <dguillett1[ at ]austin.rr.com> wrote in message news:OeMeUHp3GHA.4560[ at ]TK2MSFTNGP05.phx.gbl...
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
dguillett1[ at ]austin.rr.com
"Epinn" <someone[ at ]example.com.NO_SPAM> wrote in message
news:uyZNV8s1GHA.4752[ at ]TK2MSFTNGP05.phx.gbl...
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" <bob.NGs[ at ]somewhere.com> wrote in message
news:uTFQrNM1GHA.2196[ at ]TK2MSFTNGP06.phx.gbl...
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" <biffinpitt[ at ]comcast.net> wrote in message
news:uMEwFRF1GHA.1256[ at ]TK2MSFTNGP04.phx.gbl...
[Quoted Text] > 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(A1),1)&":"&DATE(YEAR(A1),MO
NTH(A1)+1,0))),2)<6,ROW(INDIRECT(DATE(YEAR(A1),MONTH(A1),1)&":"&DATE(YEAR(A1
),MONTH(A1)+1,0)))))
>
> Biff
>
> "Biff" <biffinpitt[ at ]comcast.net> wrote in message
> news:uO8anLE1GHA.4816[ at ]TK2MSFTNGP06.phx.gbl...
> > Shorter still: (ATP required)
> >
> > =WORKDAY(EOMONTH(A1,0)-7,5)
> >
> > Biff
> >
> > "Bob Phillips" <bob.NGs[ at ]somewhere.com> wrote in message
> > news:uAqEdlC1GHA.4796[ at ]TK2MSFTNGP03.phx.gbl...
> >> 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(EOMONTH(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 Tools>Add-Ins..., and check Analysis
> >> Toolpak.
> >>
> >> "Epinn" <someone[ at ]example.com.NO_SPAM> wrote in message
> >> news:ue9jFyB1GHA.4748[ at ]TK2MSFTNGP04.phx.gbl...
> >> 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/date_formulas_and_functions_in_microsoft_excel.htm
> >>
> >> If link broken try this ...... http://tinyurl.com/mrzcc
> >>
> >>
****************************************************************************
> >>
> >> I will stay tuned.
> >>
> >> Epinn
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >
> >
>
>
|
|
It was only deleted from his view/newsreader
This is Usenet
Steve
On Sat, 23 Sep 2006 00:35:38 +0100, Epinn <someone[ at ]example.com.NO_SPAM> =
=
wrote:
[Quoted Text] > 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 forg=
ot =
> 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" <dguillett1[ at ]austin.rr.com> wrote in message =
> news:OeMeUHp3GHA.4560[ at ]TK2MSFTNGP05.phx.gbl...
> Since your system date is wrong, I am taking the liberty of deleting y=
our
> message.
> Some do this on purpose to make sure that their message is at the top.=
=
> Did
> you?
>
|
|
You are forgiven. I only deleted from my computer.
--
Don Guillett
SalesAid Software
dguillett1[ at ]austin.rr.com
"Epinn" <someone[ at ]example.com.NO_SPAM> wrote in message
news:OsXNR$p3GHA.1848[ at ]TK2MSFTNGP06.phx.gbl...
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" <dguillett1[ at ]austin.rr.com> wrote in message
news:OeMeUHp3GHA.4560[ at ]TK2MSFTNGP05.phx.gbl...
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
dguillett1[ at ]austin.rr.com
"Epinn" <someone[ at ]example.com.NO_SPAM> wrote in message
news:uyZNV8s1GHA.4752[ at ]TK2MSFTNGP05.phx.gbl...
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" <bob.NGs[ at ]somewhere.com> wrote in message
news:uTFQrNM1GHA.2196[ at ]TK2MSFTNGP06.phx.gbl...
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" <biffinpitt[ at ]comcast.net> wrote in message
news:uMEwFRF1GHA.1256[ at ]TK2MSFTNGP04.phx.gbl...
[Quoted Text] > 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(A1),1)&":"&DATE(YEAR(A1),MO
NTH(A1)+1,0))),2)<6,ROW(INDIRECT(DATE(YEAR(A1),MONTH(A1),1)&":"&DATE(YEAR(A1
),MONTH(A1)+1,0)))))
>
> Biff
>
> "Biff" <biffinpitt[ at ]comcast.net> wrote in message
> news:uO8anLE1GHA.4816[ at ]TK2MSFTNGP06.phx.gbl...
> > Shorter still: (ATP required)
> >
> > =WORKDAY(EOMONTH(A1,0)-7,5)
> >
> > Biff
> >
> > "Bob Phillips" <bob.NGs[ at ]somewhere.com> wrote in message
> > news:uAqEdlC1GHA.4796[ at ]TK2MSFTNGP03.phx.gbl...
> >> 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(EOMONTH(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 Tools>Add-Ins..., and check Analysis
> >> Toolpak.
> >>
> >> "Epinn" <someone[ at ]example.com.NO_SPAM> wrote in message
> >> news:ue9jFyB1GHA.4748[ at ]TK2MSFTNGP04.phx.gbl...
> >> 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/date_formulas_and_functions_in_microsoft_excel.htm
> >>
> >> If link broken try this ...... http://tinyurl.com/mrzcc
> >>
> >>
****************************************************************************
> >>
> >> I will stay tuned.
> >>
> >> Epinn
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >
> >
>
>
|
|
If you could do more then that, let me know how!<bg>
--
Regards,
RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------
"Don Guillett" <dguillett1[ at ]austin.rr.com> wrote in message
news:eU04GGx3GHA.696[ at ]TK2MSFTNGP03.phx.gbl...
You are forgiven. I only deleted from my computer.
--
Don Guillett
SalesAid Software
dguillett1[ at ]austin.rr.com
"Epinn" <someone[ at ]example.com.NO_SPAM> wrote in message
news:OsXNR$p3GHA.1848[ at ]TK2MSFTNGP06.phx.gbl...
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" <dguillett1[ at ]austin.rr.com> wrote in message
news:OeMeUHp3GHA.4560[ at ]TK2MSFTNGP05.phx.gbl...
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
dguillett1[ at ]austin.rr.com
"Epinn" <someone[ at ]example.com.NO_SPAM> wrote in message
news:uyZNV8s1GHA.4752[ at ]TK2MSFTNGP05.phx.gbl...
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" <bob.NGs[ at ]somewhere.com> wrote in message
news:uTFQrNM1GHA.2196[ at ]TK2MSFTNGP06.phx.gbl...
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" <biffinpitt[ at ]comcast.net> wrote in message
news:uMEwFRF1GHA.1256[ at ]TK2MSFTNGP04.phx.gbl...
[Quoted Text] > 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(A1),1)&":"&DATE(YEAR(A1),MO
NTH(A1)+1,0))),2)<6,ROW(INDIRECT(DATE(YEAR(A1),MONTH(A1),1)&":"&DATE(YEAR(A1
),MONTH(A1)+1,0)))))
>
> Biff
>
> "Biff" <biffinpitt[ at ]comcast.net> wrote in message
> news:uO8anLE1GHA.4816[ at ]TK2MSFTNGP06.phx.gbl...
> > Shorter still: (ATP required)
> >
> > =WORKDAY(EOMONTH(A1,0)-7,5)
> >
> > Biff
> >
> > "Bob Phillips" <bob.NGs[ at ]somewhere.com> wrote in message
> > news:uAqEdlC1GHA.4796[ at ]TK2MSFTNGP03.phx.gbl...
> >> 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(EOMONTH(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 Tools>Add-Ins..., and check Analysis
> >> Toolpak.
> >>
> >> "Epinn" <someone[ at ]example.com.NO_SPAM> wrote in message
> >> news:ue9jFyB1GHA.4748[ at ]TK2MSFTNGP04.phx.gbl...
> >> 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/date_formulas_and_functions_in_microsoft_excel.htm
> >>
> >> If link broken try this ...... http://tinyurl.com/mrzcc
> >>
> >>
****************************************************************************
> >>
> >> I will stay tuned.
> >>
> >> Epinn
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >
> >
>
>
|
|
I thought he was trying to archive microsoft.public.excel.newusers and had to delete posts with a bad date. I don't really need to know what other posters do with the stuff on their computer.
If anyone wanted to know why I had 2008, all one had to do was ask.
Glad I didn't have a "heart attack." ;)
Epinn
"RagDyeR" <ragdyer[ at ]cutoutmsn.com> wrote in message news:eGyRDBz3GHA.600[ at ]TK2MSFTNGP05.phx.gbl...
If you could do more then that, let me know how!<bg>
--
Regards,
RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------
"Don Guillett" <dguillett1[ at ]austin.rr.com> wrote in message
news:eU04GGx3GHA.696[ at ]TK2MSFTNGP03.phx.gbl...
You are forgiven. I only deleted from my computer.
--
Don Guillett
SalesAid Software
dguillett1[ at ]austin.rr.com
"Epinn" <someone[ at ]example.com.NO_SPAM> wrote in message
news:OsXNR$p3GHA.1848[ at ]TK2MSFTNGP06.phx.gbl...
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" <dguillett1[ at ]austin.rr.com> wrote in message
news:OeMeUHp3GHA.4560[ at ]TK2MSFTNGP05.phx.gbl...
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
dguillett1[ at ]austin.rr.com
"Epinn" <someone[ at ]example.com.NO_SPAM> wrote in message
news:uyZNV8s1GHA.4752[ at ]TK2MSFTNGP05.phx.gbl...
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" <bob.NGs[ at ]somewhere.com> wrote in message
news:uTFQrNM1GHA.2196[ at ]TK2MSFTNGP06.phx.gbl...
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" <biffinpitt[ at ]comcast.net> wrote in message
news:uMEwFRF1GHA.1256[ at ]TK2MSFTNGP04.phx.gbl...
[Quoted Text] > 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(A1),1)&":"&DATE(YEAR(A1),MO
NTH(A1)+1,0))),2)<6,ROW(INDIRECT(DATE(YEAR(A1),MONTH(A1),1)&":"&DATE(YEAR(A1
),MONTH(A1)+1,0)))))
>
> Biff
>
> "Biff" <biffinpitt[ at ]comcast.net> wrote in message
> news:uO8anLE1GHA.4816[ at ]TK2MSFTNGP06.phx.gbl...
> > Shorter still: (ATP required)
> >
> > =WORKDAY(EOMONTH(A1,0)-7,5)
> >
> > Biff
> >
> > "Bob Phillips" <bob.NGs[ at ]somewhere.com> wrote in message
> > news:uAqEdlC1GHA.4796[ at ]TK2MSFTNGP03.phx.gbl...
> >> 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(EOMONTH(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 Tools>Add-Ins..., and check Analysis
> >> Toolpak.
> >>
> >> "Epinn" <someone[ at ]example.com.NO_SPAM> wrote in message
> >> news:ue9jFyB1GHA.4748[ at ]TK2MSFTNGP04.phx.gbl...
> >> 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/date_formulas_and_functions_in_microsoft_excel.htm
> >>
> >> If link broken try this ...... http://tinyurl.com/mrzcc
> >>
> >>
****************************************************************************
> >>
> >> I will stay tuned.
> >>
> >> Epinn
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >
> >
>
>
|
|
Had you going there, didn't I? <G>
--
Don Guillett
SalesAid Software
dguillett1[ at ]austin.rr.com
"Epinn" <someone[ at ]example.com.NO_SPAM> wrote in message
news:%23dNxvt43GHA.696[ at ]TK2MSFTNGP03.phx.gbl...
I thought he was trying to archive microsoft.public.excel.newusers and had
to delete posts with a bad date. I don't really need to know what other
posters do with the stuff on their computer.
If anyone wanted to know why I had 2008, all one had to do was ask.
Glad I didn't have a "heart attack." ;)
Epinn
"RagDyeR" <ragdyer[ at ]cutoutmsn.com> wrote in message
news:eGyRDBz3GHA.600[ at ]TK2MSFTNGP05.phx.gbl...
If you could do more then that, let me know how!<bg>
--
Regards,
RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------
"Don Guillett" <dguillett1[ at ]austin.rr.com> wrote in message
news:eU04GGx3GHA.696[ at ]TK2MSFTNGP03.phx.gbl...
You are forgiven. I only deleted from my computer.
--
Don Guillett
SalesAid Software
dguillett1[ at ]austin.rr.com
"Epinn" <someone[ at ]example.com.NO_SPAM> wrote in message
news:OsXNR$p3GHA.1848[ at ]TK2MSFTNGP06.phx.gbl...
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" <dguillett1[ at ]austin.rr.com> wrote in message
news:OeMeUHp3GHA.4560[ at ]TK2MSFTNGP05.phx.gbl...
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
dguillett1[ at ]austin.rr.com
"Epinn" <someone[ at ]example.com.NO_SPAM> wrote in message
news:uyZNV8s1GHA.4752[ at ]TK2MSFTNGP05.phx.gbl...
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" <bob.NGs[ at ]somewhere.com> wrote in message
news:uTFQrNM1GHA.2196[ at ]TK2MSFTNGP06.phx.gbl...
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" <biffinpitt[ at ]comcast.net> wrote in message
news:uMEwFRF1GHA.1256[ at ]TK2MSFTNGP04.phx.gbl...
[Quoted Text] > 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(A1),1)&":"&DATE(YEAR(A1),MO
NTH(A1)+1,0))),2)<6,ROW(INDIRECT(DATE(YEAR(A1),MONTH(A1),1)&":"&DATE(YEAR(A1
),MONTH(A1)+1,0)))))
>
> Biff
>
> "Biff" <biffinpitt[ at ]comcast.net> wrote in message
> news:uO8anLE1GHA.4816[ at ]TK2MSFTNGP06.phx.gbl...
> > Shorter still: (ATP required)
> >
> > =WORKDAY(EOMONTH(A1,0)-7,5)
> >
> > Biff
> >
> > "Bob Phillips" <bob.NGs[ at ]somewhere.com> wrote in message
> > news:uAqEdlC1GHA.4796[ at ]TK2MSFTNGP03.phx.gbl...
> >> 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(EOMONTH(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 Tools>Add-Ins..., and check Analysis
> >> Toolpak.
> >>
> >> "Epinn" <someone[ at ]example.com.NO_SPAM> wrote in message
> >> news:ue9jFyB1GHA.4748[ at ]TK2MSFTNGP04.phx.gbl...
> >> 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/date_formulas_and_functions_in_microsoft_excel.htm
> >>
> >> If link broken try this ...... http://tinyurl.com/mrzcc
> >>
> >>
****************************************************************************
> >>
> >> I will stay tuned.
> >>
> >> Epinn
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >
> >
>
>
|
|
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" <bob.NGs[ at ]somewhere.com> wrote in message news:uTFQrNM1GHA.2196[ at ]TK2MSFTNGP06.phx.gbl...
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" <biffinpitt[ at ]comcast.net> wrote in message
news:uMEwFRF1GHA.1256[ at ]TK2MSFTNGP04.phx.gbl...
[Quoted Text] > 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(A1),1)&":"&DATE(YEAR(A1),MO
NTH(A1)+1,0))),2)<6,ROW(INDIRECT(DATE(YEAR(A1),MONTH(A1),1)&":"&DATE(YEAR(A1
),MONTH(A1)+1,0)))))
>
> Biff
>
> "Biff" <biffinpitt[ at ]comcast.net> wrote in message
> news:uO8anLE1GHA.4816[ at ]TK2MSFTNGP06.phx.gbl...
> > Shorter still: (ATP required)
> >
> > =WORKDAY(EOMONTH(A1,0)-7,5)
> >
> > Biff
> >
> > "Bob Phillips" <bob.NGs[ at ]somewhere.com> wrote in message
> > news:uAqEdlC1GHA.4796[ at ]TK2MSFTNGP03.phx.gbl...
> >> 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(EOMONTH(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 Tools>Add-Ins..., and check Analysis
> >> Toolpak.
> >>
> >> "Epinn" <someone[ at ]example.com.NO_SPAM> wrote in message
> >> news:ue9jFyB1GHA.4748[ at ]TK2MSFTNGP04.phx.gbl...
> >> 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/date_formulas_and_functions_in_microsoft_excel.htm
> >>
> >> If link broken try this ...... http://tinyurl.com/mrzcc
> >>
> >>
****************************************************************************
> >>
> >> I will stay tuned.
> >>
> >> Epinn
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >
> >
>
>
|
|
|