| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Date formula not picking up 1st of the month (weekday) | Excel Discussion (Misc queries) | |||
| WEEKDAY() function: display TEXT not numeric weekday | Excel Discussion (Misc queries) | |||
| Weekday formula calculating to end of month | Excel Worksheet Functions | |||
| Weekday Formula | Excel Discussion (Misc queries) | |||
| Difficult formula SUMPRODUCT,MATCH,WEEKDAY | Excel Worksheet Functions | |||
Hybrid Mode
