Business Days / Work Days only / Date Math

[dl]

I spent forever trying to find a way to perform date math that would tell me
which records had a date that was X # of days before today, not including
weekends. Most solutions involved a slew of vba that never seemed to work.

Then I found this post:
http://www.xtremevbtalk.com/archive/index.php/t-241949.html

Which uses 2 DateDiff functions to calculate the # of business from one date
to another. I inserted my field name and a now() to make it dynamic and
BOOM. Simple -perfect.

In case that post goes down:
datediff("d",startdate,enddate) - (datediff("ww",startdate,enddate)*2)

Just wanted to share...

 

[MGFoster]

I spent forever trying to find a way to perform date math that would tell me
which records had a date that was X # of days before today, not including
weekends. Most solutions involved a slew of vba that never seemed to work.

Then I found this post:
http://www.xtremevbtalk.com/archive/index.php/t-241949.html

Which uses 2 DateDiff functions to calculate the # of business from one date
to another. I inserted my field name and a now() to make it dynamic and
BOOM. Simple -perfect.

In case that post goes down:
datediff("d",startdate,enddate) - (datediff("ww",startdate,enddate)*2)

Just wanted to share...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

When run for the month of Feb, 2009:

datediff("d",#2/1/09#,#2/28/09#) - (datediff("ww",#2/1/09#,#2/28/09#)*2)

The result is 21 days.

In REALITY there are only 20 work days (really week days 'cuz any
possible holidays are not included in the above formula) in Feb, 2009.

Experience has shown that the best solution to the work days problem is
using a date table with all the possible holidays listed, or not listed,
depending on your needs. Sometimes the date table is made up of just
the actual work dates.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSZSHnYechKqOuFEgEQJCpgCgnLzccwxqchuJCBjNCpG/1Sdz7RQAmwX8
o/Y0sYOgWobZT6AIasR9I1wj
=a6Tr
-----END PGP SIGNATURE-----