Date -1 excluding weekends

[Maleficent]

i need to pull data for "yesterday" but excluding weekends

i found the following solution on another thread:

Format(IIf(Weekday(Date())=2,DateAdd("d",-3,Date()),DateAdd("d",-1,Date())),"mm/dd/yyyy")

it worked perfectly at first however...

it only seems to work in the second half of the month. the first half of the month it returns no results, then roughly mid-month it works again.

any ideas?:banghead:

 

[Minty]

Sound like you are suffering from UK / US date formatting issues. Have a look here for some explanations and help http://allenbrowne.com/ser-36.html

 

[Maleficent]

Thanks for the response Minty

I'm working on a national company system so don't have access to update my regional settings but assume they will be set to UK.

Not sure from link what else i can do to resolve?

many thanks

 

[Minty]

Pull in the date (unformatted) to your query and see if it's dates from the 13th onwards that ARE working.

Assuming it is then is this expression in a query or in VBA ?

 

[Maleficent]

Yeah it only started working again today (pulling in 13th)

i have

"Format(IIf(Weekday(Date())=2,DateAdd("d",-3,Date()),DateAdd("d",-1,Date())),"mm/dd/yyyy")"

in the criteria section of a query under "Date Completed" field

thanks
Dani

 

[Minty]

Right Your criteria are therefore in US format (mm/dd/yy) but your dates are stored in UK. Remove the formatting.

 

[Maleficent]

i'll give that a try

thanks for your help, much appreciated!