Formula to have date be a week day

  • Thread starter Thread starter Karin
  • Start date Start date
K

Karin

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
 
Use an
IIF(WeekDay([SignatureDate]+45=1,[SignatureDate]+43,IIF(WeekDay([SignatureDate]+45=7,[SignatureDate]+44,[SignatureDate]+45))
 
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
 
I get an error saying it contains the wrong number of arguments (I copied
directly)

KARL DEWEY said:
Use an
IIF(WeekDay([SignatureDate]+45=1,[SignatureDate]+43,IIF(WeekDay([SignatureDate]+45=7,[SignatureDate]+44,[SignatureDate]+45))

Karin said:
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
Click to expand...
Click to expand...
 
I get an error saying it contains the wrong number of arguments (I copied
directly)
Click to expand...

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)
 
Thank you! The second one works well, I changed the last number (1) to -1.

John Spencer MVP said:
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
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
Click to expand...
Click to expand...
 
Back
Top