I am trying to build a form to calculate time elapsed/time difference.
Example if one turns up for work at 1900hrs (start) and ends his duty at 0200hrs the next day (end), how much time has elapsed? I am trying to use this argument :
Elapsed: IIf([end] > [start], Format([end] - [start], "Short Time"), Format(1 - [start] + [end], "Short Time"))
Unfortunately, I am not grtting the desired result. Could anyone please assist?
Thanks
Formatting issue regarding elapsed time calculated using DateDiff().
I understand that you can specify the output value for DateDiff(). In my case I have chosen "n" for minutes. Each result in my query shows the correct calculation in terms of minutes.
[PunchIn] = 11/23/2013 8:11:28 AM
[PunchOut] = 11/23/2013 5:43:30 PM
[ShiftLength] =DateDiff("n", [PunchIn],[PunchOut]) = 572 minutes.
Now when I try to format the result in terms of H:MM (be it in a form or a report) I get varied results. I'll illustrate an example below:
=Format(([ShiftLength]/60),"0") & "." & Format(([ShiftLength] Mod 60),"00") Returns 10.32 Not correct
=Format(([ShiftLength]60),"0") & "." & Format(([ShiftLength] Mod 60),"00") Returns 9.32 This is correct but I need my result to be in the form of a decimal such as my next example
=([ShiftLength]/60) Returns 9.53333333. Getting there but how do I have this result only show two decimal points 9.53?
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.
Hi
Newbie to Access so bare with me.
I am making a database to log working hours onto for different contracts I work on.
The fields I am having a problem with are "start date and time", "End date and time" and then "Elapsed time"
I need to populate these fields in the format 13/08/2006 17:20 etc.
How do I set the date/time format to do this and then how do I calculate the elapsed time in days,hours and minutes ? (perhaps just hours might do instead of days and hours) I also need to take out non working time ie 6pm through to 8am to make the calculations correct.
Everything else works fine bu I am stuck on this now.
Thanks.
I have been trying to help a user calculate elapsed time. I referenced the GetElapsedTime from the following Microsoft article
http://support.microsoft.com/kb/210604/
It displays ok on a form or inserting on the detail in a report like it suggests
but the user would also like to total up the elapsed time on the report. Not sure how to do that with the way this function is built.
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 RelatedI have nearly achieved this from an example on the Microsoft site but am having problems with the subforms. Can anyone take a look and if possible provide me with guidance as to what I am doing wrong. The instructions are included.Any help with this would be very much appreciated. Kind regards. Bernard
View 6 Replies View RelatedI am creating query that calculates time interval so that this is used to calculate pay. Employees clock in at [time_in] and clock out at [time_out]. Sometimes time_out is on the next day of time_in.So if is substract time_out - time_in I will get a negative number.I am therefore using the following formula to extract the time interval; Interval: Format([time_out]-[time_in],"Short Time")
this gives out a result such as 3:30 when in fact I would need 3.5 to calculate pay by multiplying with rate.How do I convert 3:30 to 3.5. From the searches I get that I need to multiple 3:30 by 24. But when in put done in the query Interval: Format([time_out]-[time_in],"Short Time")*24
I am currently in the process of creating an Employee Database. I have a 'staff information' table with several fields, one of which is a 'contracted hours field'. I have a spreadsheet linked to this database and therefore require this field to be formatted correctly in hours and minutes. For example...I wish to store a decimal time of 37.5 as 37:30.
View 1 Replies View RelatedI have a database with two fields [RECEIVED] and [DISPATCHED] which unfortunately are text fields which I can't change (linked via ODBC). Time is entered in these fields as military time.
I created a query with [DISPATCHED]-[RECEIVED] which works unless the time spans different hours. Example 1605-1555 returns 50 instead of the correct 10.
Any idea how I can use a query to calculate this accurately. I am an intermediate user. Any help appreciated.
I have a query that calculates elapsed time from TimeIn and TimeOut formatted to hours and minutes. I need be able to mulitply the elapsed time by the hourly wage in order to find the hourly pay.
How do you format the time so that it is compatible to multiply with the wage?
My SQL statement is below. Thanks for your help.
SELECT tTimeCards.ValetFirstName, tTimeCards.ValetLastName, Format([TimeOut]-[TimeIn],"hh:mm") AS HoursWorked, [HoursWorked]*[tValets]![HourlyWage] AS HourlyPay
FROM tValets INNER JOIN tTimeCards ON tValets.ValetID = tTimeCards.ValetID;
ok i have 4 form fields
dateleft
timeleft
datereported
timereported
and i have a form field that needs to be calulated.
ElapsedTime
i was thinking the output in the elapsed time could be something like
4 days & 8 hours
but i have no idea how to do this, can someone please help?
Here is the table info I'm working with:
class status changeby changedate statustracking
INCIDENT-RESOLVED- Person A-2/20/2014 9:05 -
INCIDENT-RESOLVED-Person b-2/20/2014 11:57 -
INCIDENT-QUEUED-Person b-2/19/2014 13:57 - 01:24:08
INCIDENT-QUEUED-Person c-2/19/2014 15:21 - 19:29:58
INCIDENT-QUEUED-Person c-2/20/2014 10:51 - 01:06:00
I need to be able to add the statustracking time together per person per status (so person c would need 19:29:58 + 01:06:00 added together). Access keeps trying to add dates to the field or reconfigure the numbers to date ime or zeros if I use any of the data type other then Memo or text.
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 a large table with a number of fields. I have written the query that I want but can't quite work out how to get it to do the main thing I need. I have several fields that I need to generate in the report but what I want it based on is the field called Date Approved.
When the Date Approved has been Active for more than 4 weeks I want to pick it up in the report. I don't want to set a date to start from but pick up everything that has passed 4 weeks in "Date Approved".
Hi all,
i am fairly new to the use of VB so (1) I am not sure if this is possible and (2) i wouldn't know where to start really so any help is greatly appreciated. I guess that in order to achieve whet i eventually want to (assuming it is possible that is) will be based on using some element of DateDiff. I have searched the forum and whilst i now have a basic understanding of the DateDiff function, i could find nothing that would help solve my problem.
On my form - which is a continuous Form displaying a number of the same type of records - i have a number of fields including InvoiceDate and TaxInvoiceDate. What i have been trying to achieve is the following:
1. Should the number of days since the InvoiceDate and the current date exceeds a certain i would like to format the InvoiceDate field, for example, if more than 30 days but less then 45 days have elapsed then the field could be this and if greater than 45 days then this. This indicates overdue accounts, with me so far? This i have managed to implement this using Conditional Formatting.
2. However, if a date is included in the Tax Invoice Date (meaning that the account has been settled / paid) i want to return / leave the text normal colour / font. This second point i guess will not be achievable when using conditional Formatting?
So, i believe that in order to achieve both points i need to use VB? If so, can anybody help me by suggesting suitable coding and where on the form / fields this code should go.
(Grovel Time) I should add that it only recently that i started using Access and developing my own databases but i have learn't so much from this forum - who knows sometime in the future i may also be able to partake by answering queries myself;)
Regards
Alan
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:
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?
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 Relatedhow 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 an attendance database and I connect the time attendance machine db to my access db, what i am trying to do is to generate a report that shows the time in and time out for specific date. the type of attendance db is date/time.
Please see the attached screenshot db from attendance machine.
an also some time there is duplicate entry, I need to get the first and the last entry only for specific date.
I 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 Relatedso i created a system to have events booked, and i am trying to check time availability of the event room available, but i dont know what wrong. it either my query or vba code. i have attached the attachment,
View 5 Replies View RelatedI 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 RelatedHi,
Wonder if someone can help please. I'm quite new to Access so please bear with me.
I have a data field in my database consisting of both a date and time.
I then have a form containing two fields where the user can type a 'To' and 'From' date to extract the records that they are interested in. The query behind this uses the 'Between[Enter The Date] And [Enter The Date]' coding.
The problem is that because the field contains a time it doesn't return any records when I run the query.
Can anyone offer a bit of guidance on how I could ignore the time part of the field perhaps by adapting the above.
Many thanks
Chris
Hi all, I havent' been around in a while (toddler, work, getting my BS degree and new baby coming in a week and a half) so I am hopefully not asking a previously posted question.
I'm suspecting my new work isn't accurately paying for the hours worked. I made a quick simple DB where I input my punch times in short time format. There's the typical In, Out for Lunch, In from Lunch, and Out. I've got it correctly adding the times so that it reports the total hours worked per day, though, in order for it to display correctly, that total is in short time as well.
The problem is, when I group a week together, as soon as the sum of the totals reaches 24 or more, it goes back to 0, as it's on the idea that there's only 24 hours in a day.
Hopefully, it's just a formatting issue or is there a function to convert the times (or just the sum) to number of hours?
Thanks and Happy New Year to all.
-Jim