By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,124 Members | 1,945 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,124 IT Pros & Developers. It's quick & easy.

workdays with holidays

P: 1
In my Access database project, I need to determine deadline dates based on working days and excluding holidays. I have set up the table as instructed below. The code that was given is for calculating the number of days between two dates. What I need is the code that will give me a date based on a date and the number of days (can vary and be either positive or negative). Can this be modified to do this? I appreciate your help.

workingday CHAR(1) NOT NULL CHECK (workingday IN ('Y','N')) DEFAULT

Populate it with as many years as you'll ever need:

INSERT INTO Calendar (caldate) VALUES ('20000101')

WHILE (SELECT MAX(caldate) FROM Calendar)<'21001231'
INSERT INTO Calendar (caldate)
SELECT DATEADD(D,DATEDIFF(D,'19991231',caldate),
(SELECT MAX(caldate) FROM Calendar))
FROM Calendar

Set the non-working days:

UPDATE Calendar SET workingday = 'N'
WHERE DATENAME(DW,caldate) IN ('Saturday','Sunday')

Record any public holidays in the same way.

Now you can easily compute the number of working days between two

FROM Calendar
WHERE caldate BETWEEN @first_date AND @second_date
AND workingday = 'Y' ;
Aug 6 '07 #1
Share this Question
Share on Google+
1 Reply

Expert Mod 10K+
P: 12,383
DateAdd("d", #, SomeDate) will give you the date after adding # of days.
Aug 6 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.