Modules & VBA :: Count Total Working Days Of Month

Mar 5, 2014

I have a table with two fields named as "Month" and "Bank Holidays". I have put names of the month in this table for 2014 and the corresponding bank holidays of each month. Now I want to write down the piece of code that will execute when a button is pressed and check the current month of the year and then display the total working days ( After subtracting bank holidays of that month and weekends of that month from the total working days of that month).

View Replies


ADVERTISEMENT

Working Days In A Month

Feb 16, 2006

Does any one know how to calculate how many working days there are in a month
IE
Total number of days in a month minus Saturdays and Sundays

All contributions gratefully received

Thanks

View 5 Replies View Related

UDF To Count Days And Convert To Month / Week / Day

Jul 18, 2014

Recently, my parents have bought a campground and I agreed to attempt to build a database which they can useto keep track of openings, reservations and I want to be able to now calculate the price for the customer. I know I can easily create an "invoice" using the forms.

Originally, I thought this would be easy and it actually wasn't too difficult, I had everything set up to go. Then I hit a snag. The prices are by days, weeks, months and season and get slightly cheaper for each tier you go up. Charging for a week is cheaper than charging the daily rate seven times, makes sense right? it makes it difficult for me because, I'll use the example of nine days, for a stay like this you would charge for one week and two days.

I can't just convert the days stayed into a number like 1.3 weeks and multiply it by the weekly rate because it would be undercharging the customer by about $30 and this number would go up as the stay got longer. I know I could do something like an "IIf" statement but in order to cover all the variables, that would be a long equation with lots of spots where a mistake could be made.

Through doing research, and asking here, I figured that it would be best to develop a UDF which would take the number of days stayed and convert it to something which would count them and turn it into a number like "1 week, 2 days". From there I will have to figure out how to make it so that the program knows which sections to multiply by which rates to build the price. I have taken a course in Access and have a huge book relating to the subject but I'm not quite sure where to begin.

View 1 Replies View Related

Count Working Days Between 2 Dates

Nov 5, 2007

I've had it before, but can't find it.

basically something like this --

DateDiff("w", StartingDate, EndingDate)


that also makes sure date is not in tblHolidays.
anyone knows how to acomplish this ?

View 1 Replies View Related

Count Working Weeks In Selected Month

Jun 2, 2006

I think i am missing the obvious but cannot seem to get this one....

I am trying to get my form to return a value of the amount of working weeks in a month i.e. a 4 week month (like june - 4 mondays) or a 5 week month (like may - 5 mondays).

i can calculate the number of full weeks using the control source..

=Int(DateDiff("d", Date1, Date2) / 7)

but this does not work for the 5 week months?

Any help would be greatly appreciated.

View 5 Replies View Related

Modules & VBA :: Working Days Between Two Dates

Jan 26, 2015

I am trying to carryout working days between two dates (Excluding weekends Friday & Saturday) but unable to fix it correctly, However i did search a lot over internet also about built in function but all is showing i have to add it module or macro etc.So, i select the module for Access 2010 but also not finish with function.Table name is "LeaveSettlement" and column name is "Total_Wdays", what field type i have to select for result column.

Public Function LeaveSettlement(ByRef Leave_Start As Date, _
ByRef Leave_End As Date _
) As Integer
' Returns the number of weekdays in the period from Leave_Start
' to Leave_End inclusive. Returns -1 if an error occurs.
' If your weekend days do not include Saturday and Friday and
' do not total two per week in number, this function will
' require modification.

[code]...

View 8 Replies View Related

Modules & VBA :: Number Of Weekend Days Worked In A Month?

Oct 6, 2013

My subreport provides appointment stats for every month and is grouped by month in the main report. I manage to include the number of days for each month using:

=DateSerial([YearNo],[MonthNo]+1,1)-DateSerial([YearNo],[MonthNo],1)

in a text box, and it works fine.

But I don't know the syntax to get these numbers:

the number of weekend days worked in that month, and the number of weekend day not worked in that month My field is call App_date.

Tried this but does not work - the numbers it returns are incorrect (in every month): =Count(Weekday([App_Date]))

The subreport's is based on a select query with only the Appointment table. It only has four "fields" - that work fine.

They are: the appointment date and its date parts - defined in numbers.

App_date
MonthNo: DatePart("m",[App_Date])
YearNo: DatePart("yyyy",[App_Date])
DayValue: Weekday([App_Date],2)

how to get my weekend work and not work total day count?

View 13 Replies View Related

Trouble Implementing The Regular Days And Sunday Working Days?

Dec 14, 2004

Hello everyone,

I have problem with my code here. I am using this code to determine if the LogDate with a weekday of Sunday can be declare as Sunday Work or Regular Work. The 1st week of my entry can determine if the LogDate is Sunday then compute if it reaches a 7 days working, if he is absent even 1 (one) day in his previous working days within the week. His Sunday LogDate is considered as Regular Days instead of Sunday Work. Can anyone tell me what is wrong in this code?

The problem here is the computation of 7 days after 1 week. There should be a 7 days computation every week per month in able to declare Sunday Work or Regular Work. I tried this code but it did not work on 2nd week, 3rd week and 4th week of the month.

How come on 2nd week, Sunday (LogDate) is not considered as Sunday Work even he completed the 7 days (working days) per week? I need help on this ASAP Thanks

Tim

Below is the code i'm using for defining Regular days and Sunday Working Days:


Private Sub LogDate_AfterUpdate()
Dim rst As DAO.Recordset, rs As DAO.Recordset
Dim db As DAO.Database
Dim dteToday As Date, dtePrior As Date
Dim i As Integer

dteToday = LogDate
dtePrior = DateAdd("d", -6, dteToday)

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays", dbOpenSnapshot)

rst.FindFirst "[HolidayDate] = #" & dteToday & "#"
'See if the date entered is a holiday
If rst.NoMatch Then
Holiday.Value = 0
Else
Holiday.Value = 1
End If
If Weekday(dteToday) = vbSunday Then 'If dated entered is a Sunday determine if
Dim strSQL As String 'it will be a regular day or a Sunday

strSQL = "SELECT [LogDate] "
strSQL = strSQL & "FROM tblLog "
strSQL = strSQL & "WHERE [LogDate] >= #" & dtePrior & "#"
strSQL = strSQL & " AND [LogDate] <= #" & dteToday & "#"

'Create a recordset of records from date entered and 7 days prior
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

rs.MoveLast
i = rs.RecordCount 'Count the number of days between the two dates

If i = 7 Then 'If there are seven days then it is a Sunday
Sunday.Value = 1
Else
Sunday.Value = 0 'If less than 7 days then it is a regular day
End If

Else
Sunday.Value = 0
End If

DayofWeek.Value = Format(dteToday, "dddd")

End Sub

View 1 Replies View Related

Date Add Problem (calendar Days Vs Working Days)

Dec 4, 2006

I have a query and I need the records to display 3 working days before the 15 working day deadline.

I used the following in the criteria box below the received date field and it doesn't pull the correct number of workdays, it's pulling calendar days instead.

<=DateAdd("w",-12,Date())

Can anyone help, thanks for your time!!!

View 8 Replies View Related

Date Add Problem (calendar Days Vs Working Days)

Dec 4, 2006

I have a query and I need the records to display 3 working days before the 15 working day deadline.

I used the following in the criteria box below the received date field and it doesn't pull the correct number of workdays, it's pulling calendar days instead.

<=DateAdd("w",-12,Date())

Can anyone help, thanks for your time!!!

View 6 Replies View Related

Reports :: Count Number Of Days Each Week (7 Days) Person Has Worked

Jan 24, 2014

I have to count the number of days each week (7 days) that a person has worked - simple enough.

But - what if they come off the job within the week?

I have been using the DateDiff function which is fine.

In the database they have a Mobdate and a DeMobDate but if the DeMobDate falls within the week the whole thing falls apart?

View 2 Replies View Related

Modules & VBA :: Determine Year From Month Count?

Jun 16, 2014

I have a table that has a field for month represented by a number. I would like to report by month but group by year. The report would group months 1-12 into year 1, months 13-24 into year 2, months 25-36 into year3, etc..... up to possible 20 years. Is there an easy way to identify and group the year? I was thinking of adding another column to the table and when I write the values to it include a year. I would rather do it some other way as the data is spilt and would require a lot of table modifications to distributed db. It would be easier to do it with a query or some code in the front end.

View 2 Replies View Related

Modules & VBA :: Total Count Of Unique Values In A Query

Jan 13, 2015

I have the following code:

Code:
Dim rs As DAO.Recordset
Dim db As Database
Dim strSQL As String
Set db = CurrentDb
strSQL = "SELECT DISTINCT tbl_contract_lev.ContractID FROM (tbl_leverancier_gegevens INNER JOIN

[Code] ....

The result of the query is a list fo unique values but by including the "count" function in SQL seems not te be working in combination with "DISTINCT"...

View 5 Replies View Related

Modules & VBA :: Count Total Number Of Integer In A List

Oct 7, 2013

In one table, I have a few fields. One of the field is "ItemSequence" and another one is "TotalPcs"."ItemSequence" is where user key in the sequence number for one or more item. 5 example for possible content of "ItemSequence" is as following :

1) 7
2) 4,6,9
3) 5-9
4) 3,5,9, 23-25
5) 3-5, 8-10

"TotalPcs" is the total number of items key in to "ItemSequence". For the 5 example above, the related "TotalPcs" should be as following:

1) 1 (1 item, which is item 7 alone)
2) 3 (3 item which is item 4, 6 and 9)
3) 5 (5 item which is item 5, 6, 7, 8 and 9)
4) 6 (6 item which is item 3, 5, 9, 23, 24 and 25 )
5) 6 (6 item, which is item 3, 4, 5, 8, 9 and 10)

For time being, the user have to count manually to get the "TotalPcs". I wonder is there a way to calculate the "TotalPcs" by programming?

View 7 Replies View Related

Calculate Working Days Between 2 Days

Mar 14, 2006

Dear All,

I'm new to VBA coding. A code below is copied from a friend of mine and I can't make it work. How to call up this function in my form. In my form I have 3 text boxes (StartDate, EndDate and NumOfWorkDays). My form is based on a table.

Please anyone who would help me on this, kindly give me the step by step procedure as I am really novice. Thanks in advance.

'*********** Code Start **************
Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer
On Error GoTo Err_WorkingDays

Dim intCount As Integer

StartDate = StartDate + 1
'If you want to count the day of StartDate as the 1st day
'Comment out the line above

intCount = 0
Do While StartDate <= EndDate
'Make the above < and not <= to not count the EndDate

Select Case WeekDay(StartDate)
Case Is = 1, 7
intCount = intCount
Case Is = 2, 3, 4, 5, 6
intCount = intCount + 1
End Select
StartDate = StartDate + 1
Loop
WorkingDays = intCount

Exit_WorkingDays:
Exit Function

Err_WorkingDays:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select

End Function
'xxxxxxxxxxxxxxxxxxxxxxxx

Can someone please direct me to the right path.

Your help is highly appreciated.

qwerty70

View 1 Replies View Related

90 Days, But Not Current Month

Sep 7, 2005

I have a query setup:
SELECT Format([RepDate],"mmmm yyyy") AS [Date Reported],
inc_Incident.Category, Count(inc_Incident.Category)
AS CountOfCategory
FROM inc_Incident
GROUP BY Format([RepDate],"mmmm yyyy"), inc_Incident.Category,
inc_Incident.Status
HAVING (((inc_Incident.Status)<>"Cancelled"));


I'm trying to figure out the best way to get the query to return category counts per month (formatted MMMM YYYY) for only the last 3 full months (not including the current month).
If it matters Repdate is formated Long Date.

Any help people can offer?

View 8 Replies View Related

Days Lost Per Month

Jul 26, 2006

Greetings,

I need to calculate with a query how long in days per month when someone was booked off sick.

e.g. booked off 12/05/06 till 17/07/06.
total days = 66

I need this broken up into months.

e.g. 05/06 = 19 days
06/06 = 30 days
07/06 = 17 days
Total = 66 days

Please help.
Thanks.

View 1 Replies View Related

Averaging Totals Of Certain Days With A Month

Aug 2, 2007

a request came accross my desk to find holidays sales in relation to the same days of the week for other weeks. For example, Thanksgivings sales compared to each of the 2 thursdays prior and the 2 thursdays after. Anyone know of a way of pulling this data so I have it all in one query - Thanksgiving, Thanksgiving+1, +2 and -1 and -2 (the numbers representing weeks from holiday

Thanks.

Brian.

View 1 Replies View Related

How To Show All The Days In A Scpecific Month

Feb 12, 2008

Hi!

I want to show all the days in the specific month, e.g. there are 29 days in february 2008 and the records will display all the days that are available as below:
1/2/08
2/2/08
3/2/08
..
..
..
29/2/08

What is the syntax?

Thank you.

View 7 Replies View Related

Working Days

May 10, 2005

Can anyone help
I have one field that you enter a start date
the next field is the start date that adds 28 days to give a due date
but this includes saturdays and sundays which i dont want i only want the working week/day.
Please help

View 3 Replies View Related

Working Days

May 22, 2005

In my queries i have two fields

1st Field is called Termination Date
2nd Field is called Due Date:
3rd Field is called Date Completed:
4th Field is called (+/-):

Termination Date = I enter a date such as 20/05/05
Due Date = Automatically fills in using the expression below 17/06/05
The Due Date has an expression builder i.e.
Termination Notice-Due Date: [Termination Date]+28

Date Completed = I enter a data such as 18/06/05
(+/-) = Automatically fills in with -1

The problem is that Termination Notice-Due Date Automatically filled in using the above expression but it counts saturdays and sundays i dont want saturdays and sundays counted in the +28

Can this be done or iam i going nuts

please see attached this might help


Hazel

View 4 Replies View Related

Working Days

May 16, 2007

I recently ran into a problem calculating working days, i now have it working however i have a couple of other issues that i would like some assistance with.
I am looking to introduce a seperate field which would say eitehr YEs or NO if the entry is more than 5 days old. SO i know how many are over 5 days. But whenever i use > 5 i get Data type mismatch. How do i get round this. :mad:

View 1 Replies View Related

Add Only Working Days

Nov 16, 2005

Help please, im using this code

Private Sub Target_Combo_AfterUpdate()
Select Case Target_Combo
Case "Emergency"
Me.TARGET_COMPLETION_DATE = Me.ISSUE_DATE
Case "3 Days"
Me.TARGET_COMPLETION_DATE = DateAdd("d", 3, Me.ISSUE_DATE)
Case "3 Weeks"
Me.TARGET_COMPLETION_DATE = DateAdd("d", 21, Me.ISSUE_DATE)
Case "28 Days"
Me.TARGET_COMPLETION_DATE = DateAdd("d", 28, Me.ISSUE_DATE)
Case "2 Months"
Me.TARGET_COMPLETION_DATE = DateAdd("m", 2, Me.ISSUE_DATE)
Case "4 Months"
Me.TARGET_COMPLETION_DATE = DateAdd("m", 4, Me.ISSUE_DATE)
Case "6 Months"
Me.TARGET_COMPLETION_DATE = DateAdd("m", 6, Me.ISSUE_DATE)
End Select
End Sub




But i want it to add only working days (monday to friday) so if i added 3 working days on to fridays date then the date would be for wednesday

View 2 Replies View Related

Parse A String With Years Month And Days....

Dec 4, 2006

Solution to parse the strings below and get number of months
1) 10 years 5 months 5 days - Should give 5
2) 6 years 10 months 22 days - Should give 10

View 2 Replies View Related

How To Get Working Days In The Query?

Nov 1, 2006

Hi, i have a problem
I need to create a query that shows me only days which r:
1. working days
2. which r not in the table on which the query is based
Thanks in advance

View 1 Replies View Related

Calculate Working Days

May 11, 2007

I hope you can help. I have a database and I am trying to work out the working days between the start date and the end date of a programme. This will not take into account any weekends or bank holidays. I have created a separate table called tblHolidays which has a list of the Bank Holidays. The majority of it works but out of 1700 records 50 don’t. These are ones in which it starts on 1 month finished on another or has a bank holiday. It works with certain bank holidays but not all of them. Bank holidays go back to the beginning of 2006
The following is the code I have used:
Public Function WorkingDays2(Start_Date As Date, End_Date As Date) As Integer

On Error GoTo Err_WorkingDays2

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays", dbOpenSnapshot)

Start_Date = Start_Date + 1

intCount = 0

Do While Start_Date <= End_Date

rst.FindFirst "[HolidayDate] = #" & Start_Date & "#"
If Weekday(Start_Date) <> vbSunday And Weekday(Start_Date) <> vbSaturday Then
If rst.NoMatch Then intCount = intCount + 1
End If

Start_Date = Start_Date + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

I then used the SQL

Select WorkingDays2 ([start_Date], [End_Date]) As CountDays

This is driving me crazy!!:mad:

View 12 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved