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 ?
I would like to count the number of working days between two dates, NOT the calendar days, if the workers only work Monday thru Thursday. I know we could use the DateDiff function, but HOW do I count excluding Fridays, Saturdays, Sundays?
Thank you for your help.
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]...
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 2 Replies View RelatedHow can I calculate the difference between two dates but I only want to count the work days? So if was today and I wanted to go until 6/15/2015 the difference would be 5 and not 7 because I do not want to count Saturday or Sunday. Is there a special %datediff function where I would only count work days?
View 4 Replies View Related I am using Access 2013.I am trying to create a query that will count the days difference between two dates. The dates are in the same field. I want to group by Region.So:
tblRegion = RegionID
tblStatus = StatusDate
I know how to use the DateDiff when it is two different fields, but I can't figure out how to do it from the same field.
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
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!!!
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!!!
I have a form called subfrm_vactions
rowsource is a query called qryVacations
the query should calculated two things
1 - the difference between start_vac and End_vac in days to calculate the vacation period and put the value in field called Period (working well)
2-the work days which the period between the last day in Previous record (End_Vac) and the (Start_Vac) in the next record (didn't work)
the result is the difference between (End_Vac) and (Start_Vac) in the same record which i don't want
simply i want to calculate the work days.
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?
I have a form where a start date is inputted (Inputfrm , StartDate) and a form where the end date of the process is recorded (Inspectionfrm , EndDate) and these both record in the table InputTbl as StartDate and EndDate respectively.
I have created a union query which shows a list of all the dates where there is work recorded (WorkingDatesQry and the column of list of unique dates is "WorkingDate"), and as we run a highly varied schedule depending on time of the year and order numbers I cannot just use a query which says Monday-Friday or Tuesday - Saturday.What I am trying to do is to find the number of days between StartDate and EndDate where there is a date recorded in the WorkingDates query.
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
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
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
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:
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
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
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
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:
Hi,
is there any alternative of the DatDiff function, which counts only working days between two given dates?
Regards,
Diana
Hi I have a Text box that stores a date of when a problem or query is received on to a customer account, when resolved the date it is resolved is inputted in the next text box as a resolved date. My third Text box then needs to display Yes or No depending if it took less than 28 days to resolve.
Ive used a Text box and DateDiff expression to work out the ammount of days in the control source, Called query dauration then a macro to test if its less than 28 days to display the yes or no. But this is not working days Mon - Fri. Has any one any ideas?
=DateDiff("d",[Date Query Raised],[Date Query Resolved])
Macro
IIf([Query Duration]<="28","Yes","No")
Anyone help please ? I'm still fairly new to ACCESS so forgive me if I'm completely on the wrong track !
I wish to add a control button to a form that will prompt me to enter a date and then calculate and display a date nn (fixed) number of WORKING days in the future.
I've seen some functions suggested in the forum and as I'm unfamiliar with this topic, I have tried putting together some code (see below) related to an On Click event for the button. I'm having syntax problems and have been battling away at this for some time without success.
Suggestions please !
Steve
Private Sub cmdDate_Click()
Dim FirstDate As Date ' Declare variables.
Dim Number As Integer
Dim dtEndDay As Date
Dim dtinterimday As Date
Dim intcount As Integer
Dim lnginterimdate As Long
Dim intdays As Integer
Dim Msg
FirstDate = InputBox("Enter a date - dd/mm/yy")
Number = 20
dtinterimday = FirstDate
intdays = Number
Do Until intcount = Abs(intdays)
If intdays > 0 Then dtinterimday = dtinterimday + 1 Else dtinterimday = dtinterimday - 1
lnginterimdate = dtinterimday
If WeekDay(dtinterimday, 2) <> 6 And WeekDay(dtinterimday, 2) <> 7 Then
intcount = intcount + 1
End If
End If
Loop
Msg = "New date: " & dtinterimday
MsgBox Msg
End Sub
Date Recieved: Date letter in
SLA: combo box: 7 days
15 days
20 days
SLA Date: Date recieved plus 7, 15 or 20 working days.
Any ideas on how I would do this?
I have a query where I use the search criteria of =Date()+1.
However I really need this to add on a working day (i.e. excluding weekends).
Is there an easy way to do this?
Hi,
I need to work out a couple of calculations, the number of days between an engineer starting and finishing a project, the number of days between the project being put on the database in the first place and being completed by an engineer and finally, the difference between these. These maybe simple, but I have no idea tbh (still new to access).
I have a form with the date it was added onto the database, and the engineer fills in the date started and date finished as it happens.
These run off of 2 tables BookingIntbl and TechnicalServicestbl
[BookingIntbl].[Date], [TechnicalServicestbl].[Date Started] and [TechnicalServicestbl].[Date Finished]
I have then got the fields, 'Days with Engineer', 'Days in Repair Centre' and 'Day Difference' for the answers to be displayed.
Any help would be appreciated, Ben