I have seen a few modules that let you count how many business days between two dates. What I am looking for is how to count 10 business days from another field.
Say for example I have a txt box that auto populates =Date(). I want to have another txt box that will count 10 business days ahead and put that date in the box.
This is so I can have the db show a due date for reports sent in.
Anyone seen/done/have code for this?
Hi, I'm trying to get some date tracking stats...
trying to get the average number of days between two dates using formula:
=Avg(DateDiff("d",[DateRec],[DateNumb]))
where datenumb was the first date app was entered into db and
where daterec is the date the first part of app was recorded.
this works but I'm assuming it's counting straight calendar days. I was wondering is there a way to get it to count only business days and not weekends or holidays in access???
Good Afternoon All,
I am in need of you help in calculating this. Basically, I don't want the code to count zero. For example, today is 3/19/2008 - Date() should equal = 1 and not zero..
Here's some more examples:[lastfollowup] being 3/18/08
Date() - [LastFollowup] = 2
Here's my code:
DaysOpen: ((DateDiff("d",[LastFollowup],Date(),2))-(Int(DateDiff("d",[LastFollowup],Date(),2)/7))*2)+IIf(Weekday(Date(),2)>5,5-Weekday(Date(),2),0)+IIf(Weekday([LastFollowup],2)=6,1,0)-IIf(Weekday([LastFollowup],2)>Weekday(Date(),2),2,0)
Thank you,
John
I am trying to calualate the business from an Order Date. The Business Days are Monday thru Friday excluding Holidays. It is for a form that will Highlight (conditional Format) Order over five business days old that have not been completed. Has anyone done this before?
View 2 Replies View RelatedHi,
Is there any access formula that can calculate the net business days between two dates.
Regards,
Jatz
SELECT
[(2) MAX BATCH].ID_CASE,
[(2) MAX BATCH].MaxOfDT_BATCH,
NOLDBA_LOG_DISBURSEMENT_DETAIL.DT_DISBURSE
FROM
[(2) MAX BATCH] INNER JOIN NOLDBA_LOG_DISBURSEMENT_DETAIL ON
([(2) MAX BATCH].MaxOfDT_BATCH = NOLDBA_LOG_DISBURSEMENT_DETAIL.DT_BATCH) AND
([(2) MAX BATCH].ID_CASE = NOLDBA_LOG_DISBURSEMENT_DETAIL.ID_CASE);
Can someone tell me how to find the ID_CASE's where
NOLDBA_LOG_DISBURSEMENT_DETAIL.DT_DISBURSE is less
than or equal to 2 business days (M-F) of the MaxofDT_BATCH in this query?
I have a query that calculates business days. However if my end date is missing the query has error codes because the date its looking for is null. Can show me were to put the nz function.
here's the module for the business days
Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer
' "Calculating the workdays between Dates"
' Note that this function does account for holidays.
Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer
BegDate = DateValue(BegDate)
EndDate = DateValue(EndDate)
DateCnt = BegDate
EndDays = 0
Do While DateCnt <= EndDate
If Not IsNull(DLookup("HoliDate", "tblHolidays", "[HoliDate]=#" & DateCnt & "#")) Then
EndDays = EndDays - 1
End If
If Format(DateCnt, "ddd") <> "Sun" And _
Format(DateCnt, "ddd") <> "Sat" Then
EndDays = EndDays + 1
End If
DateCnt = DateAdd("d", 1, DateCnt)
Loop
Work_Days = EndDays
End Function
and here's the query
SELECT [Reques_ID], Work_days([Date_Received],[To_Director]-1) AS Turnaround_To_Director,
Work_days([To_Director],[From_Director]-1) AS Turnaround_From_Director, Work_days([To_VP],[From_VP]-1) AS Turnaround_From_VP, Work_days([Position_Number_Requested],[Position_Number_Recieved]-1) AS Turnaround_For_Position_Number, Work_days([Date_Received],[Approval_to_mgr]-1) AS Turnaround_EPS, Work_days([Date_Received],[JOIS_Posted_Date]-1) AS Turnaround_To_Posting, Work_days([Approval_to_mgr],[JOIS_Package_Return_Date]-1) AS Turnaround_For_Package_Return
INTO tbl_turnaround
FROM Artifact;
I have a date range of 1/01/2005 to 12/25/2005 for example. I need a formula to return the number of days between these dates excluding weekends. Basically I need to know how many business days are between these dates. Thanks for the help.
View 1 Replies View RelatedHello all,
I have 2 tables:
- Employee
- PackageTracker (Contains PackageNumber, date received, date sent, status)
I would like to query:
- which employee has the package
- How long have we had the package (calculate only business days)
- What is the status
I would like the things mentioned above to be ran in one query.
I am stuck with the calculations. I found a macro code to calculate the business days but I am stuck.
Please advise.
Thank you,
John
Is there a function in Access that will allow you to add business days to a date? I want to pull a date with a query and then add 5 business days to it to display in a report. Anyone know if this is possible?
Thanks.
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 got an assignment from my supervisor and I am not getting it! :(
I have a query (see result in attachment) with the following fields:
tID, emailID, emailtype and EmailDate.
Example:
A customer (tID 1297542) sends an e-mail (emailtype 1) to the company on 2-8-2005.
The company replies on that e-mail (emailtype 3) on 5-8-2005.
Problem:
I would like to create a query that shows per tID the total of days between the e-mail from the customer and the e-mail from the company (between emailtype 1 and 3).
Hi...
here's a good one...
I have created a database which allows people to enter in when they are off sick. They have to enter in the FIRST DAY of sickness and the LAST DAY of sickness. All of this information is stored in one big table - SICKNESS TABLE. There are no other date fields in that table.
I want to know how many people have been off for 5 TIMES OR MORE in a 365 day period (which may be say, from Oct 2005 to Nov 2006 - so not always in the same year).
I have managed to get a query that will count the number of times someone has been off; but that could be over, for example, 2 years.
How can I get it to tell me if someone has been off for 5 TIMES OR MORE in a 365 day period???
Thanks
Maria
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 am trying to find the expression to count the number of days excluding fridays from a given txtStartDate and txtEndDate.
i've got the expression to count the number of days but i need omit the fridays from it.
how to come up with the number of unic days worked as the DB i atached if worker works in diferent work order in the same day in the report it comes up as he work two days
View 8 Replies View RelatedRecently, 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.
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 RelatedI have 2 tables on one report, 1 is visible, the other visible = no, Is there a way to make that visible = yes when you reach a certain count (60days)...
View 1 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.
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.
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
Is there a way i can convert the weekend days into working days for example.
5/4/13 1:30 PM is on saturday what function can I use to make this day read 5/6/13 8:00 Am which is monday. Same thing for holidays.
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.
Client has asked me to create a report showing summary of monthly sales by day. That was easy. I created a query for the month the user selected and then summarized and group the data by day. Client like the result but would like to see zeros on the report for non sales days. Non sales days are days like holidays and there are no sales.
I am thinking of creating an table with 31 days of zero values and then join the two tables in a query? Or, should I create a temporary table with code and then merge the two tables which the existing query which I can then use for the report?