Calculating Time
Aug 11, 2005
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
View Replies
ADVERTISEMENT
Dec 1, 2013
I 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.
View 4 Replies
View Related
Aug 22, 2012
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 Related
Mar 8, 2007
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.
View 5 Replies
View Related
Oct 4, 2006
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,
View 1 Replies
View Related
Nov 10, 2006
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.
View 6 Replies
View Related
Jan 3, 2007
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??
View 3 Replies
View Related
Feb 22, 2006
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 Related
May 15, 2006
Apologies 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
View 4 Replies
View Related
Nov 12, 2007
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!
View 1 Replies
View Related
Nov 12, 2007
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!
View 1 Replies
View Related
Nov 12, 2007
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
View 5 Replies
View Related
Nov 3, 2014
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.
View 12 Replies
View Related
Nov 11, 2014
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?)
View 14 Replies
View Related
Dec 14, 2013
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.
View 3 Replies
View Related
Sep 11, 2014
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.
View 3 Replies
View Related
Jan 12, 2007
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.
View 6 Replies
View Related
Nov 5, 2006
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"
View 1 Replies
View Related
Jan 8, 2015
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.
View 11 Replies
View Related
Jul 8, 2015
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....
View 6 Replies
View Related
Mar 14, 2013
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 Related
Aug 2, 2014
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 Related
Feb 18, 2014
Access 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).
View 3 Replies
View Related
May 23, 2006
I have an application with a backend db on PC 1, and the same application on PC 2 linked to the backend db on PC 1 via the network.
The link works fine but the time formats are different.
On PC 1 the time format is shortime and displays as it should i.e. in 24 hour clock format
However on PC 2, opening the same database via the network, the time format is still shorttime but when you read the time within the code it comes out in AM/PM format. Also, when the defaul tiem should be #20:00:00# but this changes to #8:00:00 PM#
Weirdly though, when you just open the table, the times are in the correct shorttime format.
Guessing, it must be a setting within the main core of Access 2003 that is different between the 2 instances?
Any ideas?
This screen shot may help:
View 2 Replies
View Related
May 23, 2006
I have an application with a backend db on PC 1, and the same application on PC 2 linked to the backend db on PC 1 via the network.
The link works fine but the time formats are different.
On PC 1 the time format is shortime and displays as it should i.e. in 24 hour clock format
However on PC 2, opening the same database via the network, the time format is still short time but the format is in AM/PM or medium time.
Guessing, it must be a setting within the main core of Access 2003 that is different between the 2 instances?
Any ideas?
View 1 Replies
View Related
Nov 8, 2013
I have a database that stores information for lab testing. Each time a tech does a "step" in the test process he logs it in the table, using an input form. There are different categories, for example preparation, testing, analysis, etc, and each of those steps take time. I have the form autopopulate the date and time with NOW() evertime the form is updated. What i want to do is calculate the time it takes to do each in days. I can easily get how many days it was from now since they logged the test, =NOW()-TestDateTime. What I want to do is get the number of days it took to do each step, ie the number of days between each event. Is there a way to do this?
View 3 Replies
View Related
