I need to calculate the difference between to datetime fields. However, i need to exclude weekends, holidays and non-business hours. I also need to return the result in hours, minutes, seconds.
can this be done. if so, does anyone know how to do this, or is there code that i can implement and save for future uses.
thx,
john
I have to create a query that will reflect the number of calls and messages a person has either taken during business hours and if a message was left how long did it take them to return the message. We are open from 6:00AM - 4:30PM so my boss wants to only see how many calls a person took during business hours. now the users have to run this report daily and monthly. and have to make it accessible through a report so they can send it to the supervisors.
The two fields I have to make the calculations on is the [Date & Time Left] the time the call was made [Date Returned] if they left a message the date it was returned. Also if the call was returned in one day she wants it broken down into minutes (as in how many hours and mins was the call returned)
This is what I have so far: I noticed there was something in Excel called NETWORKDAYS?
Code:
SELECT [Message Tracking].[Date & Time Left], [Message Tracking].[Date Returned], DateDiff("h",[Date & Time Left],[Date Returned]) AS TotalHours, [Message Tracking].ID, [Message Tracking].[User Returning]
FROM [Message Tracking]
GROUP BY [Message Tracking].[Date & Time Left], [Message Tracking].[Date Returned], [Message Tracking].ID, [Message Tracking].[User Returning]
What I am trying to do is execute a function M-F every 2 hours between 4am and 6pm. The 2 hour part was easy, I just created a form with a timer, but the other part isn't working so well, here is my latest attempt.
Code:
StrTime = Time()
If Not ((Weekday(Now()) = vbSaturday) Or (Weekday(Now()) = vbSunday)) And (StrTime > "4:00" Or StrTime < "18:00") Then
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, i have a table with the fields: "employee_number", "shiftdate" "department" "basic_hours", and "over_time"
And a query named "employee_hrs" looks at the fields in this table, i would like a new field in the query to be able to calculate the total hours for all employees together but for each department.
eg the total of: basic hours + overtime for department A)
the total of: basic hours + overtime for department B)
and so on for each department
would only like to show one instance of a department (distinct) with the total hours done by all employees for that department?, if any one could please help me out that would be great!?
Let me just start out by saying that when it comes to Access, I ride on the "little bus", so I apologize in adavance if this is a lame question.
I have a table with two date/time fields. One for reporting the date and time of an issue start, the other for the date and time of an issue ending. I'm trying to find a way to get the number of hours between the two without including weekends. I found a module on mvps.org that excludes workdays. However it doesn't seem to work in this case. For instance, if I put in an issue start as 02/10/2006 7:30 am, and the issue end as 02/13/2006 6:45 am, it only counts it as one workday. It won't count the 13th as a workday until the time is >= 7:30 am.:confused:
I'm simply stumped. Any help would be greatly appreciated.
Thank you,:)
I have designed an access application that logs emails that come into a department. However, the team leader has asked me to create a report to work out if emails were answered within a 24 hour period.
Is there a method to do this. It is not something I have attempted to do within access before.
I have a problem with Microsoft access 2003. I want to design a Check in/Check out Time Data base for my university final term project. In this data base I need to show the total of the working hours of each personnel in a month.( for instance: 124 hours and 55 minutes in one month). But adding up the hours, when the time variable reaches 24 then time automatically goes back to 01:00. As a result, the correct total hours cannot be calculated....
View 1 Replies View RelatedI want to subtract 8 hours of work of the Result of employee work hours/
There is attachment...
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 setting up a Database for my company, we want to do it in our local branch first then hopefully we can role the idea out to the entire company. I have started the database already just setting up the tables I need, I think my Design is slightly off though. I am asking for the forums help if that is okay, okay here is the setup.
Areas Needed:
Employee
Projects
Clients
Employee Area:
Date of Hire
fname
lname
salary
title
timesheets
etc...
Project Area:
project #
project name
client
budget info
etc...
Client Area:
client name
addy
contact info
etc....
Project area is going to be the main hub for everthing else, the project number is the key to the entire database. client will be attached to a project via project number, employees timesheets tracked by project number, invoices printed by project number. timesheets will generate the invoices to the client. You guys get the idea, I hope that some of you guys can help me in the spare time you have.
Again, i am looking for Design assistance, i will post a sketch of my design in a couple.
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
Hi. I'm a freshmen currently in college and I'm taking business info systems right now... and well I'm just being paranoid. Anyway, i really hope this isn't at all too confusing or anything... but
For a hw assignment we where sopposed to like dl this database and just do random commands on it. Anyway, we're sopposed to save it at the end... and email the file to the professor who will grade it.
Anyway, I asked her how she would know if we actually did all the steps and shit... and how she could tell (esp if the directions where like... "1. filter this out." then "3. take out the filter."
she replied that access "saves" all the actions in a database in the document so she will be able to see all the actions that we took.
I did all the assignments, and I'm just being paranoid about my work right now... since it's the new semester and the teacher is SO NICE but very picky when it comes to grading.
My question is just to ask if anyone knows the "secret" command/function/button or whatever you do to basically do what the professor would do to grade my database / ie see that I did everything.
I just want to check to see all my work is good before I submit it to her.
Thank you so much for your time and reading this. :)
I want to use a business card scanner, and use the data for my Access 2000 order database. Most card scanners, can send data to Outlook, can you go from Outlook to Access? Any advise would be appreciate it . . . THANKS!:o
View 2 Replies View RelatedI am building a database for a Real Estate business.
I have 2 tables tblPurchasers and tblVendors.
I intend to write a query that will match the requirements of the purchasers to the properties for sales (from the vendors).
My issue is this (best illustrated via an example);
I run my Query on the 1st April and four properties are produced as being suitable for purchaser 'A'.
Purchaser 'A' immediately deems properties 1 and 2 unsuitable and views property 3 on the 6th April. Property 3 is subsequently deemed unsuitable as well.
I re-run my qury on the 10th April and five properties are produced as now being suitable for purchaser 'A'.
Purchaser 'A' has already dismissed properties 1,2 and 3 so i don't want to show him these again ....... how do I just ensure that properties 4 and 5 appear ?
Purchaser 'A' has already dismissed properties 1,2 and 3 so I don't want to show him these again ....... how do I just ensure that properties 4 and 5 appear ?
Something involving a yes/no box on a form would be the best method for me of implementing this (as it would fit in with the scematic of the database to date).
I imagine the properties (with a small photo) appearing and tick boxes headed 'Unsuitable', 'Viewed' etc be used to dismiss properties, which do then not re-appear.
MANY THANKS !
Hi,
I am trying to update a field on a form with a date that is +7 days from a date that is in another field, but the new date must only be a business day. Can anyone tell me how to do this?
Thanks
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 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?
I have a mdb file that our company has been using for years and would like to continue. But I need to update our business name and address within the file so when I do reports and invoices it reflects the correct information. I am using Access 2013.
View 14 Replies View RelatedIs there an Add in for Microsoft access that will using a gui based method, run queries, set up automated reporting (task Scheduler) in an easy to administer method. Quest Toad has a new add in Toad for Data Analysis. I am looking for something similar for access. Right now I am doing this manually via creating macros, etc. But there really should be an easier way.
Thanks
Hello 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
Hi dont know how im gonna explain this but here goes,
I have a table of businesses and each business has vacancies and consultancy time under it through related tables, so i have
Businesses
|
------------------
|.......................|
Vacancies........Consultancy
I want to be able to display all the Vacancies for one company in a form and all the consultancy in one form, i know i could do it though querys but i have 600 companies and that would be ridiculous.
Is there any way i can do it by selecting a business and been able to see all records for that business.
Thanks for your help
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.
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???