WORKDAY() and probably more

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Epinn - 09 Sep 2006 15:30 GMT

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_exc
el.htm

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

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

I will stay tuned.

Epinn

Reply to this Message

Bob Phillips - 09 Sep 2006 17:02 GMT

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.

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_exc
el.htm

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

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

I will stay tuned.

Epinn

Reply to this Message

Epinn - 09 Sep 2006 18:20 GMT

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

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.

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_exc
el.htm

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

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

I will stay tuned.

Epinn

Reply to this Message

Bob Phillips - 09 Sep 2006 18:46 GMT

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??

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)

Reply to this Message

Biff - 09 Sep 2006 20:05 GMT

Shorter still: (ATP required)

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

Biff

> Hi Epinn,
>
[quoted text clipped - 59 lines]
>
> Epinn

Reply to this Message

Bob Phillips - 09 Sep 2006 21:54 GMT

Biff,

You are anal (said with love, humour, and grace :-))

Bob

> Shorter still: (ATP required)
>
[quoted text clipped - 7 lines]
> >
> > 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))
> >
[quoted text clipped - 26 lines]
> > 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)))
> >
[quoted text clipped - 3 lines]
> >
> > Source:

http://www.officearticles.com/excel/date_formulas_and_functions_in_microsoft_exc
el.htm

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

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

> > I will stay tuned.
> >
> > Epinn

Reply to this Message

Ragdyer - 09 Sep 2006 22:01 GMT

That "love" and "grace" have *interesting* connotations!<vbg>

Signature

Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

> Biff,
>
[quoted text clipped - 13 lines]
> > >
> > > 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))
> > >
[quoted text clipped - 29 lines]
> > > 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)))
> > >
[quoted text clipped - 3 lines]
> > >
> > > Source:

http://www.officearticles.com/excel/date_formulas_and_functions_in_microsoft_exc
el.htm

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

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

> > > I will stay tuned.
> > >
> > > Epinn

Reply to this Message

Bob Phillips - 10 Sep 2006 11:26 GMT

I love his work, and I gracefully acknowledge his skill and expertise <vbg>

Signature

HTH

Bob Phillips

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

> That "love" and "grace" have *interesting* connotations!<vbg>
> > Biff,
[quoted text clipped - 14 lines]
> > > >
> > > > 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))
> > > >
[quoted text clipped - 29 lines]
> > > > 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)))
> > > >
[quoted text clipped - 3 lines]
> > > >
> > > > Source:

http://www.officearticles.com/excel/date_formulas_and_functions_in_microsoft_exc
el.htm

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

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

> > > > I will stay tuned.
> > > >
> > > > Epinn

Reply to this Message

Ragdyer - 10 Sep 2006 16:46 GMT

Ahhh! ... the verbal agility of the British.<g>

Signature

> I love his work, and I gracefully acknowledge his skill and expertise <vbg>
>
[quoted text clipped - 23 lines]
> > > > >
> > > > > 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))
> > > > >
[quoted text clipped - 34 lines]
> > > > > 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)))
> > > > >
[quoted text clipped - 3 lines]
> > > > >
> > > > > Source:

http://www.officearticles.com/excel/date_formulas_and_functions_in_microsoft_exc
el.htm

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

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

> > > > > I will stay tuned.
> > > > >
> > > > > Epinn

Reply to this Message

Biff - 09 Sep 2006 22:29 GMT

> Biff,
> You are anal ....

Yes, I know. I think I have OCD (perfectionist). Just wait 'til I get this
stuff figured out!

Biff

> Biff,
>
[quoted text clipped - 76 lines]
>> >
>> > Epinn

Reply to this Message

Biff - 09 Sep 2006 22:09 GMT

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

> Shorter still: (ATP required)
>
[quoted text clipped - 65 lines]
>>
>> Epinn

Reply to this Message

Bob Phillips - 10 Sep 2006 11:25 GMT

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

Signature

HTH

Bob Phillips

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

> 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
>
[quoted text clipped - 9 lines]
> >>
> >> 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))
> >>
[quoted text clipped - 26 lines]
> >> 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)))
> >>
[quoted text clipped - 3 lines]
> >>
> >> Source:

http://www.officearticles.com/excel/date_formulas_and_functions_in_microsoft_exc
el.htm

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

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

> >> I will stay tuned.
> >>
> >> Epinn

Reply to this Message

Rate this thread: