Weekday formula?
 

Hi maybe someone can help me.

I want to calculate the day previous to a given date ...
for example if I enter
06/11/2007 I want to return 05/11/2007
However if the day entered is a Monday I want to return the previous
Friday's date
for example if I enter
05/11/2007 I want to return 02/11/2007

I have been trying an IF but to no avail. Any suggestions greatfully
received :) I figure it must be possible. Thanks

Weekday formula?
 

=WORKDAY(A1,-1)

If you get a #NAME error:

ToolsAdd-ins, check Analysis Toolpak

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"EJB" wrote in message ...
| Hi maybe someone can help me.
|
| I want to calculate the day previous to a given date ...
| for example if I enter
| 06/11/2007 I want to return 05/11/2007
| However if the day entered is a Monday I want to return the previous
| Friday's date
| for example if I enter
| 05/11/2007 I want to return 02/11/2007
|
| I have been trying an IF but to no avail. Any suggestions greatfully
| received :) I figure it must be possible. Thanks

Weekday formula?
 

Assuming that you will never have a Satuday or Sunday date then try:

=A1-(WEEKDAY(A1,2)=1)*3

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"EJB" wrote in message
...
Hi maybe someone can help me.

I want to calculate the day previous to a given date ...
for example if I enter
06/11/2007 I want to return 05/11/2007
However if the day entered is a Monday I want to return the previous
Friday's date
for example if I enter
05/11/2007 I want to return 02/11/2007

I have been trying an IF but to no avail. Any suggestions greatfully
received :) I figure it must be possible. Thanks

Weekday formula?
 

I'm assuming you will only enter a date that is Monday through Friday.

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

If you enter a date that is either Sat or Sun the formula will return an
error.

--
Biff
Microsoft Excel MVP


"EJB" wrote in message
...
Hi maybe someone can help me.

I want to calculate the day previous to a given date ...
for example if I enter
06/11/2007 I want to return 05/11/2007
However if the day entered is a Monday I want to return the previous
Friday's date
for example if I enter
05/11/2007 I want to return 02/11/2007

I have been trying an IF but to no avail. Any suggestions greatfully
received :) I figure it must be possible. Thanks

Weekday formula?
 

One way

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



--


Regards,


Peo Sjoblom




"EJB" wrote in message
...
Hi maybe someone can help me.

I want to calculate the day previous to a given date ...
for example if I enter
06/11/2007 I want to return 05/11/2007
However if the day entered is a Monday I want to return the previous
Friday's date
for example if I enter
05/11/2007 I want to return 02/11/2007

I have been trying an IF but to no avail. Any suggestions greatfully
received :) I figure it must be possible. Thanks

Weekday formula?
 

A non WORKDAY() solution might be:

=A1-CHOOSE(WEEKDAY(A1,2),3,1,1,1,1,1,2)
or
=A1-MOD(328681,WEEKDAY(A1,2)+6)

--
Dana DeLouis


"EJB" wrote in message
...
Hi maybe someone can help me.

I want to calculate the day previous to a given date ...
for example if I enter
06/11/2007 I want to return 05/11/2007
However if the day entered is a Monday I want to return the previous
Friday's date
for example if I enter
05/11/2007 I want to return 02/11/2007

I have been trying an IF but to no avail. Any suggestions greatfully
received :) I figure it must be possible. Thanks

Weekday formula?
 

"Dana DeLouis" wrote in message
...
=A1-MOD(328681,WEEKDAY(A1,2)+6)

And yet another magic number pulled out of your black bag! <g

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

Weekday formula?
 

Thanks very much - so simple when you know how :) You have saved me hours!

"Niek Otten" wrote:

=WORKDAY(A1,-1)

If you get a #NAME error:

ToolsAdd-ins, check Analysis Toolpak

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"EJB" wrote in message ...
| Hi maybe someone can help me.
|
| I want to calculate the day previous to a given date ...
| for example if I enter
| 06/11/2007 I want to return 05/11/2007
| However if the day entered is a Monday I want to return the previous
| Friday's date
| for example if I enter
| 05/11/2007 I want to return 02/11/2007
|
| I have been trying an IF but to no avail. Any suggestions greatfully
| received :) I figure it must be possible. Thanks