|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
May 8th, 2009, 07:20 PM
posted to microsoft.public.access
|
|||
|
|||
Formula to have date be a week day
Hi, I have a text box on a subform that is calculating a date based on
another field (=[SignatureDate]+45). If the date is on a weekend, it really needs to be on the Friday before. Is there anyway I can do this in the text box formula? TIA 
|
|
#2
May 8th, 2009, 07:31 PM
posted to microsoft.public.access
|
|||
|
|||
|
Formula to have date be a week day
Use an
IIF(WeekDay([SignatureDate]+45=1,[SignatureDate]+43,IIF(WeekDay([SignatureDate]+45=7,[SignatureDate]+44,[SignatureDate]+45)) "Karin" wrote: Hi, I have a text box on a subform that is calculating a date based on another field (=[SignatureDate]+45). If the date is on a weekend, it really needs to be on the Friday before. Is there anyway I can do this in the text box formula? TIA
|
|
#3
May 8th, 2009, 07:50 PM
posted to microsoft.public.access
|
|||
|
|||
|
Formula to have date be a week day
Two methods that should work
=SignatureDate+45+IIF(WeekDay(SignatureDate+45)=6,-1,IIF(WeekDay(SignatureDate+45)=1,-2,0)) or =SignatureDate + 45 + Choose(WeekDay(SignatureDate+45),-2,0,0,0,0,0,1) John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Karin wrote: Hi, I have a text box on a subform that is calculating a date based on another field (=[SignatureDate]+45). If the date is on a weekend, it really needs to be on the Friday before. Is there anyway I can do this in the text box formula? TIA
|
|
#4
May 8th, 2009, 07:53 PM
posted to microsoft.public.access
|
|||
|
|||
|
Formula to have date be a week day
I get an error saying it contains the wrong number of arguments (I copied
directly) "KARL DEWEY" wrote: Use an IIF(WeekDay([SignatureDate]+45=1,[SignatureDate]+43,IIF(WeekDay([SignatureDate]+45=7,[SignatureDate]+44,[SignatureDate]+45)) "Karin" wrote: Hi, I have a text box on a subform that is calculating a date based on another field (=[SignatureDate]+45). If the date is on a weekend, it really needs to be on the Friday before. Is there anyway I can do this in the text box formula? TIA
|
|
#5
May 8th, 2009, 08:44 PM
posted to microsoft.public.access
|
|||
|
|||
|
Formula to have date be a week day
On Fri, 8 May 2009 11:53:17 -0700, Karin
wrote: I get an error saying it contains the wrong number of arguments (I copied directly) I think Karl misplaced some parentheses. Try IIF(WeekDay([SignatureDate]+45)=1,[SignatureDate]+43,IIF(WeekDay([SignatureDate]+45)=7,[SignatureDate]+44,[SignatureDate]+45)) or perhaps a bit more simply [SignatureDate]+Switch(Weekday([SignatureDate])=5,43,Weekday([SignatureDate])=4,44,True,45) -- John W. Vinson [MVP]
|
|
#6
May 8th, 2009, 08:57 PM
posted to microsoft.public.access
|
|||
|
|||
|
Formula to have date be a week day
Thank you! The second one works well, I changed the last number (1) to -1.
"John Spencer MVP" wrote: Two methods that should work =SignatureDate+45+IIF(WeekDay(SignatureDate+45)=6,-1,IIF(WeekDay(SignatureDate+45)=1,-2,0)) or =SignatureDate + 45 + Choose(WeekDay(SignatureDate+45),-2,0,0,0,0,0,1) John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Karin wrote: Hi, I have a text box on a subform that is calculating a date based on another field (=[SignatureDate]+45). If the date is on a weekend, it really needs to be on the Friday before. Is there anyway I can do this in the text box formula? TIA
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
