Alternative to WORKDAY when Analysis ToolPak not loaded

[Paul D. Simon]

I have a simple formula in a workbook that indicates the previous
workday from today's date:

=WORKDAY(TODAY(),-1)

So, for example, if today is Monday, the formula above gives last
Friday's date. Or, if today is Wednesdsay, it indicates yesterday's
date (Tuesday). (Holidays are not pertinent here.)

Unfortunately, a few people to whom I distribute this workbook do not
have Analysis ToolPak installed, so that particular cell (and all the
cells that refer to that cell) shows #NAME?

Is there an alternative formula to WORKDAY that I can use that will
indicate the previous workday from today? (Again, Holidays are not
pertinent as we will always be looking for a Monday-Friday date).

Many thanks.

 

[Jarek Kujawa]

=IF(AND(WEEKDAY(TODAY())>=3,WEEKDAY(TODAY())<=7),TODAY()-1,IF(WEEKDAY
(TODAY())=2,TODAY()-3,TODAY()-2))

HTH

 

[Bernie Deitrick]

Paul,

=TODAY()-IF(MOD(TODAY(),7)=2,3,1)

should work.

HTH,
Bernie
MS Excel MVP

 

[Jarek Kujawa]

this is what I meant
just was not able to phrase it
;-)

 

[Glenn]

If you assume that TODAY() is never Sunday.

Paul,

=TODAY()-IF(MOD(TODAY(),7)=2,3,1)

should work.

HTH,
Bernie
MS Excel MVP

 

[Glenn]

I have a simple formula in a workbook that indicates the previous
workday from today's date:

=WORKDAY(TODAY(),-1)

So, for example, if today is Monday, the formula above gives last
Friday's date. Or, if today is Wednesdsay, it indicates yesterday's
date (Tuesday). (Holidays are not pertinent here.)

Unfortunately, a few people to whom I distribute this workbook do not
have Analysis ToolPak installed, so that particular cell (and all the
cells that refer to that cell) shows #NAME?

Is there an alternative formula to WORKDAY that I can use that will
indicate the previous workday from today? (Again, Holidays are not
pertinent as we will always be looking for a Monday-Friday date).

Many thanks.

=TODAY()-CHOOSE(MOD(TODAY(),7)+1,1,2,3,1,1,1,1)

 

[HARSHAWARDHAN. S .SHASTRI]

Try following formula,

=IF(WEEKDAY(TODAY())=2,TODAY()-3,IF(WEEKDAY(TODAY())=1,TODAY()-2,TODAY()-1))
--
HARSHAWARDHAN.S.SHASTRI

Pl do not forget to press "YES" button if post found useful.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

 

[psimon]

Jarek, Bernie and Glenn,

Thank you all for your very speedy and very helpful responses. All of
your formulas worked absolutely perfectly! (And yes, Glenn, TODAY()
would never be a Sunday.)

Thanks very, very much to you all!

Paul

 

[psimon]

Thank you Harshawardhan. Your formula works perfectly as well.
Thanks for your fast response - I appreciate it.

 

[Glenn]

Jarek, Bernie and Glenn,

Thank you all for your very speedy and very helpful responses. All of
your formulas worked absolutely perfectly! (And yes, Glenn, TODAY()
would never be a Sunday.)

Thanks very, very much to you all!

Paul

Actually, I'm pretty sure that TODAY() will be Sunday for 24 hours every week ;-)


To assume that the "few people to whom I distribute this workbook" would NEVER
open it on Sunday may seem logical now, but you know what happens when we assume...