Display yesterday's date but only for weekdays?

[totalnatal]

Hi,

I have the following function : =TODAY()-1

The problem is that if we're monday it gives me sunday's date whereas
I only want weekdays so in this case friday.

Any idea on how to mod the function to do that?

thanks

 

[Mike H]

Hi,

Try this.

=WORKDAY(NOW(),-1)

If you get a #MAME error then

Tools|Addins and check the analysis toolpak.

Mike

 

[Gary''s Student]

=TODAY()-CHOOSE(WEEKDAY(TODAY()),2,3,1,1,1,1,1,1)

 

[Shane Devenshire]

I hope you don't get a "MAME" error!

 

[Mike H]

OOPS tpyos R'US

Mkie

I hope you don't get a "MAME" error!

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

 

[Shane Devenshire]

Sorry if this posts twice but had a crash when I clicked Post

This depends on what days you do this for, if you only use the file Monday
to Friday then

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

If on the otherhand you might open the file on Sunday then the above will
need to be modified.

 

[totalnatal]

Sorry if this posts twice but had a crash when I clicked Post

This depends on what days you do this for, if you only use the file Monday
to Friday then

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

If on the otherhand you might open the file on Sunday then the above will
need to be modified.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire









- Show quoted text -

Hey,

Thanks, I think it works but was wondering how you constructed it,
especially regarding the use of MOD function. Thanks

 

[rachash21]

=TODAY()-CHOOSE(WEEKDAY(TODAY()),2,3,1,1,1,1,1,1)

THIS WORKS !

 

[Ron Rosenfeld]

THIS WORKS !

You could also use =workday(today(),-1)
If you are using a version of Excel prior to 2007, and receive the #NAME! error, look at HELP for the WORKDAY function for instructions on installing the Microsoft Analysis ToolPak