Date()-1 excluding Weekends

[JD316632]

RESOLVED: Date()-1 excluding Weekends

I have a query that pulls data from a Due Date column based on the date. I want to see what I missed from yesterday.

However on Monday it shows me Sunday. I want it to instead skip Saturday/Sunday and use Friday if it is Monday but I cannot for the life of me accomplish this. I've used Google and attempted to use Weekday, DatePart and VBA code but cannot get something that seems to simple to work.

• The Due Date field is in date/time format (1/1/2010 4:30 PM) I have it formatted to just the date as it made pulling the data based on the criteria easier (Format([Due Date],"mm/dd/yyyy")
• The criteria currently used with the Due Date field is Format((Date())-1,"mm/dd/yyyy")

If you need any additional information please let me know. Thanks in advance for any assistance.

 

[raskew]

Hi -

I got kind of lost on your formatting explanation. Provided your date fields are stored in date/time data format (not text), give this a try:

Public Function fGetWorkdays2(pstart As Date, pend As Date) As Integer
'************************************************
'Purpose:   Compute number of workdays (Mon - Fri)
'           between pStart and pEnd
're:        http://www.access-programmers.co.uk/forums/showthread.php?t=164254&highlight=simplified
'Coded by:  raskew
'Input:    ? fGetWorkdays2(date(), #4/1/09#)
'Output:    51
'************************************************

   fGetWorkdays2 = 7 - WeekDay(pstart) + 5 * (DateDiff("ww", pstart, pend) - 1) + WeekDay(pend) - 1

End Function

HTH - Bob

 

[JD316632]

You could try criteria of:

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

Exactly what I needed, thanks!

 

[Maleficent]

i used this solution:

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

in my own database after searching for the same issue as OP

it worked perfectly for the first couple of weeks and then one day just stopped working!?

i thought at first it might have been to do with the change of month from oct to nov but it hasnt worked since

any ideas?