Alternative to WORKDAY when Analysis ToolPak not loaded

P

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.
 
J

Jarek Kujawa

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

HTH
 
B

Bernie Deitrick

Paul,

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

should work.

HTH,
Bernie
MS Excel MVP
 
G

Glenn

Paul said:
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)
 
H

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.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 
P

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
 
P

psimon

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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top