Calculating without Holidays

[DataChick]

I need to know how to calculate NetWorkDays so that it doesn't include holidays.

Is there a way to have the function reference a table that lists all the holidays to not include in the total days?

Thank you.

"The most likely way for the world to be destroyed, most experts agree, is by accident. That's where we come in; we're computer professionals. We cause accidents."
-Nathaniel Borenstein

 

[jfgambit]

How many holidays are there? Can they change?

"I know what you're t'inkin', ma petite. Dat Gambit... still de suave one, no?"

 

[DataChick]

There are about seven total but some of them (Memorial Day, Thanksgiving) are going to have a different date every year...which is why I was hoping I could have it reference a table that the user could update as needed.

Thank you.

"The most likely way for the world to be destroyed, most experts agree, is by accident. That's where we come in; we're computer professionals. We cause accidents."
-Nathaniel Borenstein

 

[jfgambit]

If this is a simple calculation and the holiday number stays the same then I would suggest the following:

me.YouTotalNeworkDaysField = (me.yourtotaldays - 7)

If you had the following field in a form

TotalDays
TtlDaysnoHol

and added the code to the after update event of the Totaldays field the field (say 245) then TTlDaysHol would = 238.

Is that what you are looking for?


"I know what you're t'inkin', ma petite. Dat Gambit... still de suave one, no?"

 

[DataChick]

Actually, I am calculating the difference in two dates to get the amount of days a container was at a location. I can't count the day if it was a holiday...so some will include the holiday and some will not.

"The most likely way for the world to be destroyed, most experts agree, is by accident. That's where we come in; we're computer professionals. We cause accidents."
-Nathaniel Borenstein

 

[jfgambit]

Ok datacjick no I got ya...

Create a new table with your holidays then try this code

Dim rsCnt as Variant

rsCnt = DCount("[HolidayDate]","Holidate", "[HolidayDate] Between #" & Me.[FirstDateField] & "# And #" & Me.[LastDateField] & "#&quot

this will give you a number that you can use in a calcualtion, i.e.:

TotalDays = DateDiff("d",[FirstDateField],[LastDateField)) - rsCnt

HTH


"I know what you're t'inkin', ma petite. Dat Gambit... still de suave one, no?"