Number of Workdays in a month....

[Guest]

Hi There

I need to calculate the number of workdays in a month for a totals query and my current formula seems to have some bugs....here's what I have..

WorkDaysInMonth: Day(DateAdd("m",1,[Date])-Day([Date])

Using this formula, if a record is entered for example, on the 30th or 31st of January, it's returning 30 and 29 days in the month. In addition, I need to calculate the number of weekdays instead of the total number of days. Any suggestions

Much thanks

Lynndyhp

 

[Cheryl Fischer]

You will find some excellent functions for doing Workday math/identification
at:

http://www.mvps.org/access/datetime/date0012.htm


hth,
--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

Hi There!

I need to calculate the number of workdays in a month for a totals query

and my current formula seems to have some bugs....here's what I have...

WorkDaysInMonth: Day(DateAdd("m",1,[Date])-Day([Date]))

Using this formula, if a record is entered for example, on the 30th or

31st of January, it's returning 30 and 29 days in the month. In addition, I
need to calculate the number of weekdays instead of the total number of
days. Any suggestions?

 

[Guest]

Thanks for your response...

Is there a way to do just a formula in a query? I'm not familiar with VBA

Thanking in advance

Lynndyhop

 

[Cheryl Fischer]

The functions provided at the link work in essentially the same way a
formula would - you just have to supply the values to make them work.
Simply copy all of the code at that link into a public Module. Save and
close the module.

You can then use any of the functions found in the code in a query. In your
query, use the dhCountWorkdaysA() to find the number of working days between
two dates. Here is an example which will return all of the WorkDays in
March, exclusive of holidays:

WorkdaysInMarch: Function dhCountWorkdaysA(#3/1/2004#, #3/31/2004#) As
Integer

You can substitute the two date literals in the above example with fields in
a query:

WorkdaysBetween: Function dhCountWorkdaysA([BeginDate], [EndDate]) As
Integer