I would like to add time elapsed since an entry was made excluding weekends public holidays and calculating 8 hours a day (from 6am to 2 pm) since the entry was made till the generation of the report.
How shall I approach this in terms of programming ?
Is there a way, in a query or via the use of a module, to calculate the workdays between two dates excluding holidays without needing to maintain a separate table with the holidays listed out?
For example, I would like it to calculate 21 work days for the month of May. However, I don't want to have to go in the first of each year and manually list out all the holidays for the year. Is there a way for the Holidays table to just contain the number of holidays in a given month (i.e. in December we get 3 holidays (Christmas Eve, Christmas Day, and New Years Eve).
Currently I use a module to calculate DueDate, excluding weekends and holidays. I store all the holiday in a tblHoliday and reference this table in the module. A formula then calculated the DueDate. This works great in an access form, but I now realize that I need that value stored so it appears on the table as well, but do not know how to accomplish this.
View 9 Replies View RelatedI am trying to limit the results of a SQL query by date. I would like to take the current date from the system clock, move back one day, check to make sure it's not a weekend or holiday, then use the result date to limit the results in my Query. If the current date minus one day happens to land on a weekend, the date picked should be the Friday before the weekend. If the current date minus one day lands on a holiday, it should be the date before the holiday as long as it is not a Saturday or Sunday (on another holiday) .
Here is my SQL query right now.
SELECT DISTINCT (Mid(ClientDiv.Client_Division,1,3)) AS ABC, RTIClientTracker.EMB_OOB, RTIClientTracker.OOB_Fixed
FROM ClientDiv INNER JOIN RTIClientTracker ON ClientDiv.ID = RTIClientTracker.Client_Division
WHERE (((RTIClientTracker.Division_Region)='RTI') AND ((RTIClientTracker.Cut)>=Date()-1))
ORDER BY (Mid(ClientDiv.Client_Division,1,3));
It limits what it selects by using the current Date minus one day.
I need to skip over Saturdays, Sundays and Holidays.
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 am trying to Calculate the Date difference between "Material Submit Data" and "Current Date" excluding Weekends.
View 2 Replies View RelatedI have a query that is looking to calculate staff absence.
Absence Start date & Return to work date
Looking to calculate number of days (not a problem) but to exclude weekends ie saturday and sunday ????
Not sure if this can be done --- any assistance would be greatly received.
thanks and regards
ian watson
Yarm Cleveland UK
I'm using access 2007 and have some date fields in my forms, i'd like to be able to blank out weekends and public holidays from the calender/date selector in these fields so theres no way those dates can be selected. Is this possible? Thanks.
View 1 Replies View RelatedI am using the function below to calculate a field that excludes weekends and holidays. The weekends are excluded as it is now, but when I try to add in code to exclude holidays I am getting errors. The code for the holidays is in bold and a couple of the errors are Loop without Do so I remove the Loop then I get a Else without If.
Code:
Option Compare Database
Public Function WorkingDays(Due_Date As Date, Result_Date As Date) As Integer
'-- Return the number of WorkingDays between Due_Date and Result_Date
On Error GoTo err_workingDays
[Code] ....
I want VbA code to find a date 1 less than todays date taking into account weekends/Bank Holidays.
View 8 Replies View RelatedHello all,
I am a bit stuck in my query. I need to setup a Next Follow-up date based on the last follow-up date excluding weekends.
For example: Last Follow-up: 1/10/08.
Next follow-up should be 5 days after that. So it should be: 01/17/08.
I have a calculated field that calculates the number of days (Counting off weekends) but I can't seem to have the date setup correctly.
Thanks
I have a field in my report [Idle]
This has a time in a HH:MM:SS format (so 01:38:23)
What I need to be able to do is sum this greater than 24 hours and keep the format of HH:MM:SS - ideally I need to do this in the form field that I'll be using to sum it.
I am starting to create a resourcing database that needs to be able to work with dates and times and perform calculations on them.
I need to work out the number of hours. I have two date/time fields Day_Start_Time and Day_End_Time. I want to calculate the difference between the two in hours and then multiply by the number of working days.
I tried this: Number_Of_Hours: DateDiff("hh",[Day_Start_Time],[Day_End_Time])*[Number_of_days]
But get #Func! when I run the query.
I have a database consisting of two tables. One is "articles" and the other is "tasks". To put it simply, I would like to find how much time the article spends in tasks, but one article can have many tasks, and they often (but not always) overlap. Tasks have a start and end date field.
View 8 Replies View RelatedI have a Floating Holiday that doesn't happen every year how do I incorporate this in Access 2010, I have a report that calculates Paid Time Off for employees and when they have a year that has a Floating Holiday or doesn't have a floating holiday, how do I incorporate that into a query or report without losing the calculations from the previous year reports.
for example, this year a floating holiday is allowed so my calculations work, but next year if a floating holiday isn't allowed how do I make it a 0 without losing this years data.
I tried tblFLOATINGHOLIDAY with several years listed (at first used Yes/No) and a field that you either put 8 or 0 (8 hours or 0 hours). Then I used a query combining tblEMPLOYEES and tblFLOATINGHOLIDAY with citeria for FLOATINGHOLIDAY to be 2015 so all the records would show 8 under FLOATING HOLIDAY. But it comes up with no records.
Looking to have a time field that a user can enter how long they worked an issue. Format should be ##:##, but I'm afraid that if I use text, calculations will be impossible.
How does someone do this if it's expected that the value will frequently go over 24 hours?
Any Help!
I have fields that are time formated 99/99/99" "00:00;0;_ and I want to subtract them.
I need to get the results in two forms; first in Days and second in Hours. I have used the obvious subtract one from the other and it gives me days in a rounded numbeer, but the hours is not working out.:confused:
DB
I want to create a query that will calculate the following:
When a user enters a start time and an end time in 2 seperate fields (eg. 06:00 and 08:00) I want to automatically return how many hours fall between 00:00 to 01:00, 01:00 to 02:00, 02:00 to 03:00 etc through the 24 hour clock.
To give you a background why I want this, I'm creating a labour planning database that calculates the nuber of people available each hour in a warehouse. So when a user inputs 06:00 to 08:00 for overtime work I need the database to tell me 1 hour falls between 06:00 to 07:00 and 1 hour between 07:00 to 08:00.
I'm really struggling to come up with a solution to this one, any help would be appreciated.
This has probably been raised before but here goes anyway.
I have two fields in a table: time_in and time_out, both as date/time fields and formatted for short time with a mask in the form.
I need to calculate the time spent, i.e. time_in 10.00, time_out 12.15 therefore 2hours 15minutes.
any help much appreciated.
Jim
Hi Guys
I need to create a method of calculating -week number,hours worked per week and a leiu hours total.
Explanation
Users enter their worked hours per day. I need to calculate the total hours worked per user per week and if this is greater or less than their contracted hours a record of this figure over a continual time period (or year).
Variables :- Time worked,Holiday hours,Leiu hours taken,Sick leave, Other authorised leave.
I realise that i'm asking on a lot but working for a charity has financial limits.
Attached the basic database-so far
Thanks in advance for your help
Row
I created a time clock module where you input the time in and time out and it takes the difference of the two to give total time worked. However the boss wants the time worked represented in hours and tenths of hours not minutes. How can I take the time, seperate the hours...find out how many tenths of an hour worked and then add the hours and tenths together.
View 2 Replies View RelatedHi,
I am trying to show the time difference between 2 times but the calculation is not working correctly.
First I work out the totaltime on mins between 2 dates using datediff, then I am trying to convert to hours and minutes like :
Total_Time: Format([Sumoftotaltime_mins]/60,"00") & ":" & Format([SumofTotaltime_mins] Mod 60,"00")
This is working until I have something like :
03:00
15:45
25:20
this should work out as 44 hours and 5 mins but for some reason it is showing as 43 hours and 5 mins.
why would this be ?
Hi,
I have a bit of a problem. I am making a program in access 2002. In my program you should punch in hours:minutes, this is for keeping track of how long a job has taken to perform. The problem is that it could exceed 23:59, it could take up to a week or even more. In my database we have already put in a lot of info where it doesn't exceed the 23:59, I would like to keep this posts and just to put in new ones that I could use further on when I am about to take out reports, where I have to calculate time. Is there someone who knows what to do ??
Regards
Greger
I need to calculate in the tbleJobs table, the amount of the jobs total amount and place the result in the JobsTotalAmount field.
This has to be a manual calculation. Not an on event calculation.
Data is coming from two table:
tblLimos has the hourly rate.
tblJobs has the start time, the finish time and the total amount field.
I am using a database and need a field capable of storing times that will be greater than 24 hours.
I understand there is no "ready made" way of dealing with this in Access, so what's the best way?
The catch is I am using VBA code in Excel to populate the table, the value comes from a cell (in Excel formatted as "[h]:mm").
I have a hourly report I need to run and one of the records will always be filled with a name of the employee. I need to hardkey some data in every day and instead of changing who is there that day I would like to be able to enter in, for example, sales data in a seperate record and when I go to create the report it will exclude any employee names that have 0 sales data or a blank record. so the table would be something like:
Employee.....Sales....
John.............0
Steve............__
Mathew..........1
So when I run the report it will only pull Mathew's name and sales information and leave John and Steve off the report.