i have a opened date[default value =Now()]
and
resolved date[default value =Now()].
i would like to calculate the time elapsed between the two dates. it is normally on the same day, but there are instances where it can run past midnight.
I have a combo box which looks up a set of values from a table, some of these I have got to store, others I merely want to work with and calculate from. These columns are:
DutyNumber
WeekendOrBHPayment
Sun-ThursNightsPayment
FriSatBHNightsPayment
OTRate
WRDRate
AHDifference
SKPayment
UnSocPayment
Now for most of these, I 'think' I have it planned where they will go or how they will be used, but the OTRate is flummoxing me a wee bit.
On the form, I have an OT(time) field - the idea being that the user will enter the hours and minutes of overtime they have done (short time format - is this correct?)
Then, I would like the form to calculate the cash value (has to be stored in a field) by multiplying the hours/mins worked by the OT Rate - - but I don't want to store the OTRate unless I really have no other option. Once the calculation has been done, if the Rate is then 'forgotten' then that is no issue, as the only time it would be needed is if the field is revisited to amend any errors (so I am thinking the calculation should be done as an After Update event on the OT(time) field?)
I have my main menu form that shows the current time and uses the OnTimerEvent for this. (Set to 1000) Issue is that when I open another form it keeps showing Calculating. It does not seem to affect performance but keeps the status bar from showing the control explanations.
View 10 Replies View RelatedI have a working dB which can calculate a shift duration and sum total all shifts worked within a period for the purpose of producing a labor report for payroll. I have successfully used the DateDiff function and converted the minutes to HH:MM on my form and reports. Now I want to calculate elapsed time for a specific period within a shift, I'll call it OtherHours and I am aiming to calculate a portion of time that meet the following conditions below. I am using field names of [PunchIn] and [PunchOut] and both are of type General Date.
IF [PunchOut] ISNOT Saturday,Sunday
EXIT FUNCTION
ELSE
IF [PunchOut] ISNOT Between Midnight and 0559 hours
EXIT FUNCTION
ELSE
DATEDIFF ("n", <MIDNIGHT>, [PunchOut])
My thoughts are to solve the DateDiff portion and then figure out how to apply the conditions within the IF statements.
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'm trying to separate LastName, FirstName into separate fields : FirstName and LastName. The code I have is working for the most part. But I had to create a separate query with the replace statement to get rid of the ","... Looking at the data now, I had people with names like Mac Buren, Tony and I'm only picking up the Mac part in my query.
Code:
SELECT Left([Employee Name],InStr([Employee Name]," ")) AS qryLastName, Right([Employee Name],Len([Employee Name])-InStrRev([Employee Name]," ")) AS [First Name], Employees.*
FROM Employees
ORDER BY Left([Employee Name],InStr([Employee Name]," "));
Hi there,
I need some help with calculating login/logout times. I have an ODBC link set up for the raw data in AVAYA. The problem is that the login/logout times are reflected as the number of seconds that have passed since January 1st, 1970. I need to know what that amount in seconds is in a time format.
I have worked out the number of days difference between my rowdate and the start date of 01/01/1970 so I am left with how many seconds have passed since midnight of the selected date.
For example, the selected date is 3/7/07. The Login time is 5410 (seconds passed since 3/7/07 00:00:00). How do I figure out what time that was (hh:mm:ss)?
Any help would be greatly appreciated.
Rick.
Hello,
I have been struggling with this for 2 weeks now. I give up and am now going to ask for help. I am trying to calculate time.
I have been trying to do this in the query.
I have 4 fields
Start
Finish
Start2
Finish2
I need to figure out how many minutes total a letter was worked on. The reason for the 2 time sets is because they could start a letter and then stop and then come back to it later.
So I think what I need to do is figure out how many hours and minutes for the 1st Start/Finish set, then figure out the hours and minutes for the 2nd Start/Finish set and then add the two together.
Can anyone help me out here. Like I said I am at my whits end.
Oh the fields are formatted in the Date/Time data type.
Thanks
Becky
I'm trying to write a query to figure out the time something was delivered. I have the following information to work with:
Estimated arrival time: 13:00
Loaded Time : 14:00
Trip Time : 2.5hr - - how do I conver this to Hr's & Min.?
I need to subtract the ETA & the Load time and then subtract half the trip time.
Any help would be appreciated
Thanks,
I have a file that collects the time data of the shop employees. It records time in (start of day), time out (lunch start), time in (lunch end) and time out (end of day).
Sample of the table:
In/OutEmployee IDDateTime
0316 11/2/20067:00:34 AM
01244 11/2/20067:05:00 AM
1316 11/2/200612:00:43 PM
11244 11/2/200612:01:21 PM
0316 11/2/200612:30:43 PM
01244 11/2/200612:34:25 PM
1316 11/2/20063:30:46 PM
11244 11/2/20063:34:34 PM
0 = In, 1 = Out
How can I calculate the total hours worked per day(First In + First Out + (Second In + Second Out).
I appreciate any help.
I have the following feilds in a query: Timein Timeout
I need a subtotal feild that totals the two across midnight
I also need a Lunch feild
Then I need a total feild with the subtotal and total subtracted
I tried using this expression please help anyone if you can
Format([Timein] -1 -[Timeout], "Short Time") this created my subtotal feild
then I had a made lunch feild with 30 mins of time into it
then i tried Format([subtotal] -1 -[lunch], "Short Time") and called this one my total box.
The first one worked the second reported an error. Please any suggestions??
I have attached a really simple db where in a query I'm trying to determine the elapsed time between A sent date and a received date. Can some tell me what I'm doing wrong? Thanks..
View 1 Replies View RelatedApologies if this has been asked before but I have a problem with being able to format a field to accept an hours and minutes value where the hours is likely to be greater than 23.
I have a form that is related to the number of hours that people spend in respite care. Entry and exit dates are provided in a standard way - date and time - and I have a calculating field that calculates the number of hours and minutes that a person spends at the respite centre based on the entries made in the entry and exit fields.
Because the calculating field is based on a module and I need to have the calculated amount included in the table, I have set up a VBA code to copy the result of the calculation into a controlled field elsewhere in the form.
However, I also need the form to do a simple calculation whereby hours spent away from the centre are subtracted from the total hours calculated by the module, so I need the copied value to be in a format where the calcualtion is able to be performed.
In a nutshell I guess I am looking for a time format for the field that will allow the recipt of a value greater than 23 hours.
Of course perhaps I need a more sophisticated VBA script to perform the calculation behind the scenes but I admit to not being particularly at ease with VBA scripting. If anybody has any suggestions about either a format or a suggestion for a VBA script, I would be very appreciative.
Thanks in advance,
Chris Montgomery
Hi, I wonder if someone can help me with the following problem:
I have a table with 2 columns,start time and end time, both containing time values in a four digit format, eg 0930 being 9:30am etc.
I have made a query to convert these into the standard format (eg 0930 becomes 09:30), using left/right and & functions.
Now I use the timevalue function to convert the string , eg 09:30m into a recongnised time value.
The problem is now I want to find the difference between the start and end times- but when i try a simple end_time - start_time formule it returns a long number which i dont understand, rather than just giving me the difference betwen the two times.
Im sure there is a simple way to do this which I dont know, can anyone help me out? Thanks in advance!
Hi, I wonder if someone can help me with the following problem:
I have a table with 2 columns,start time and end time, both containing time values in a four digit format, eg 0930 being 9:30am etc.
I have made a query to convert these into the standard format (eg 0930 becomes 09:30), using left/right and & functions.
Now I use the timevalue function to convert the string , eg 09:30m into a recongnised time value.
The problem is now I want to find the difference between the start and end times- but when i try a simple end_time - start_time formule it returns a long number which i dont understand, rather than just giving me the difference betwen the two times.
Im sure there is a simple way to do this which I dont know, can anyone help me out? Thanks in advance!
I need help to calculate time difference from the record above where ID is same. I have attached an example in excel with this request where I have if statement doing exacly what I want to do in Access. I have a table in Access that has three columns - as below
ID, Reg, TimeDiff
66646, 14/06/2007 21:33,
66646, 15/06/2007 06:03, 8:30:00
66646, 15/06/2007 12:20, 6:17:00
66646, 15/06/2007 15:08, 2:43:00
67844, 14/06/2007 04:39,
67844, 14/06/2007 09:05, 4:26:00
67844, 14/06/2007 09:08, 0:03:00
67844, 14/06/2007 16:27, 4:09:00
If ID is equal to the ID in record above then TimeDiff is equal to reg minus reg in the above recored, else blank.
Hope someone can help me with this.
Thanks, GS
I run a Pass through query against a SQL-Server from Access 2010.I have a field TimeWorked which has values like 1899-12-30 03:30:00.000, for 3:30 hours and a field PaymentPerHour with values like 10.50 (for EUR 10,50).The result schould be 36,75 (36.75).
I tried to calculate like this: Round(convert(varchar,[TimeWorked],104)*1440*[PaymentPerHour ]/60,2)AS TtlPayment
In an Access query it works with the connected table in the accb, but not against the server.I get a lot different errors when trying to convert into int, datetime, etc. but could not solve my problem.
I have a table in access that has 1 ) persons ID and ) log date/time 3) direction in or out
I need to calculate the amount of time spent by the person in the office. e.g User with ID 1 will come in at 8 in the morning and go out after an hour then come in again and go out. Till he leaves out for the day. I need to find the first time he came in and last time he went out and find the difference for the hours he worked
The data is in Device logs table.
Hi, I have never had to do any maths with access before, so I don’t know where to start!
What I need to do is calculate remaining warranty information for products.
In the table I have 3 columns. the 1st column is "Purchase date (dd/mm/yy)" 2nd column is "Total warranty period (years)" and the 3rd column is "Remaining warranty"
The 3rd column is the one that will display how long is left to run! How do I achieve this?
Thanks.
I have created a database in Access where parents of students may come in and volunteer and receive free time credit from their employer (those companies who participate).
The database runs fine: Main table (demographics; student table; signIn/signOut table; and Volunteer Duty (a combo box that list all of the approved volunteer duties to qualify for the time credit) Every thing works perfect with the exception of my signIn/signOut form. After struggling with this for countless hours, I have settled for a manual entry of the signIn and signOut time, entered at the time of their start/stop times. I would like for it to be an automated function, but I have had such a difficult time up to this point I will settle for this method. What I NEED is to figure out how to make my "total vol hours today" field calculate the total volunteer hours at the time the parent "signOut".
If some of you Access MVP's can help with this I will be very grateful.
I awaite your answers, and THANKS IN ADVANCE.
signed: "head spinning like-a-top"
I am using access 2013 and have an issue creating a query with employee hours for a two week period.
For starters I have a table with the following:
Employee Number, Date, Time in, Time out, Description, Rate Code, Week end, Pay Period. (there are a couple others but they are not needed for the query)
My table is set up this way so that each employee can clock in and out 1-4 times a day based on what they are doing. For example I may work from 6-8 in the office then 8-10 in the field. The office has one rate code and the field has a different one so i cant just be clocked in from 6-10.
At the end of the pay period I create a report and send it to an accouting firm who does the payroll. Right now I have to manually enter in each employees time. I was hoping that by using this table I could create a query that will give me the calculations I need in order to create the report.
In my query (to Start) I need to have the following with it set up per pay period.
Employee Number Rate Code Regular Hours Overtime Hours
How to set it up so that it calculates the hours based per rate code with the given pay period.
I have been trying for about 8 hours to calculate elapsed time between two fields into a third field in Access 2007. Unfortunately, the expression
IIf(IsNull)([Final Suspense]),DateDiff("d",[Routing Status Date],Date()),DateDiff("d",[Routing Status Date],[Final Suspense]))
is not working. Image enclosed....
I have to fields in military time. I need to calculate and have the result in decimal format to the nearest .25 minutes.
View 1 Replies View RelatedAccess Query. I am creating a time sheet / pay roll database and I want to be able to get a total of the daily hours in a query.
For example I have 'Mon Start' and 'Mon Finish' for Mondays in/out times and I have a 'Mon Total' which gives me the total hours worked for Monday.
The problem I have is that Mon Total only works if the hours are say between 07:00 and 17:00, anything after midnight (00:00) like 21:00 to 07:00 and 'Mon Total' goes crazy !!
At the moment 'Mon Total' is the result of CDate 'Mon Finish' - 'Mon Start' (bit rough I know).
how to be able to enter time in access form the same way as if would be an access table (1p = 1:00 PM; 1.25 = 1:25 AM etc)
View 1 Replies View RelatedI have a date/time text field on a form with the General Date format and a combo box next to it that has sequencial times as the row source (IE. 12:45 AM, 1:00 AM, 1:15 AM, 1:30 AM, ETC.) When the user chooses a time in the combo box, I want the time portion of the text box to be updated with the chosen time in the combo. I have tried a few things but cant seem to get it right.
View 4 Replies View Related