Calculate buisness days rather than calendar days

  • Thread starter Thread starter Erick C
  • Start date Start date
E

Erick C

Hi Everybody -
I have a quick question. I have a field in a query that gives me the
previous day using:
Issue Date: Date()-1
The issue date is always supposed to be the previous buisness day.
The formula that I have now gives me the previous calendar day. This
is no good for Monday, since I get Sunday's date.
I tried a couple of things with no success, like playing with Weekday
and anything else I could find in the builder that looked like it
could help. How can I get my field to populate the previous buisness
day's date? I was using Date() since it gave me the mm/dd/yyyy format
that I need.

Thanks for the help!
 
You could do something simple like:

Issue Date: IIf(weekday(#1/26/2010#,vbSaturday)>3, #1/26/2010#-1,
#1/26/2010# - weekday(#1/26/2010#,vbSaturday))

HOWEVER (big however) - how about Holidays? What happens when Christmas
happens on a weekday? How about when Christmas happens on a weekend but
celebrated (work-wise) or a Friday or Monday?

You might just want to set up a table of non-business days and the nunber of
days to subtract based on the date. Populate this table way into the future
and check it as part of the calculations.
 
Erick -

This will check to see if today is Monday, and if so, subtract 3 days,
otherwise subtract one day. This will not work for holidays. If you want to
test for holidays, then you will need to build a table with your company
holidays and the date to use for those holidays...

Here is the simple one:

IIF(DatePart("w",Date())=2,Date()-3,Date()-1)
 
Back
Top