Default Value - Calculate Date Using If... --> #Name?

[EdAROC]

I'm attempting to optimize the user interface on a form that prompts for a date for which a report will run.

Normally the employee will run the report using yesterday's date. So, I set the default to = Date()-1. This works fine. I want to take this a step further. There is usually no activity on the weekends, so, when the report is run on Monday the default would be Friday's date. Here's the formula I entered:

=if(Weekday(Date())>2,Date()-1,Date()-1-Weekday(Date()))

[Weekday: Sunday=1, Monday=2,...] If the Weekday is 3 thru 7 (Tue thru Sat), then subtract 1, else subtract 1 and subtract the Weekday value. i.e. If someone runs the report on Monday the date is Monday minus 1 minus 2 = Friday. Sunday would be -1 -1 = Friday.

However, when I run the form I get "#Name?" in the text box.
What is messing up the value?

 

[PHV]

And what about this ?
=Date()-1-IIf(Weekday(Date())>2,0,Weekday(Date()))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886