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 RepliesI 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 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....
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?
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 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
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 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 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".
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 table "main" with 2 colums (main_id,date_out). This table has a link to the table "refill". This table has 3 colums (refill_id, main_id, refill_date).
Example data:
TABLE MAIN
main_id - data_out
01 - 01/01/2006
TABLE REFILL
refill_id - main_id - refill_date
01 - 01 - 10/01/2006
02 - 01 - 16/01/2006
03 - 01 - 21/01/2006
When I make a simple selectquery with the fields main.date_out and refill.refill_date I become the the next result.
01/01/2006 - 10/01/2006
01/01/2006 - 16/01/2006
01/01/2006 - 21/01/2006
Now the problem.
I have to calculate the days between the main and refill dates
01/01/2006 - 10/01/2006 ( is the same as above)
10/01/2006 - 16/01/2006
16/01/2006 - 21/01/2006
suggestions????......thanks
i'm trying to create a query which looks at the field dateEntered and then returns the previous sunday date of that week.
ex.
dateEntered returnedSundayDate
09/26/2006 09/24/2006
are there built in access functions to figure this out in a query?
please help.
In access in one field I have one date and in another field I have another date. I want to calculate the number of years.,months and days elapsed between these two date. e.g. suppose in one field I have 30.06.2005 and in other 20.04.2004 then it should show 1 year 2 months and 10 days.
View 14 Replies View RelatedHello,
I'm trying to get a number of days between an employee's hire date and today's date. The employee is supposed to change levels every year until reaches 6 yrs. The sample employee hired on 11/13/2001 and today's date is 1/13/2006, so it should be only 4 yrs (level 04). I tried assigning the levels with this function DateDiff('d',[STEP TABLE].[Hire Date],Date()) AS [Total days] and also with this code below.
Switch([Total Days] Between 0 And 365,"Lvl 01",[Total Days] Between 366 And 730,"Lvl 02",[Total Days] Between 731 And 1095,"Lvl 03",[Total Days] Between 1096 And 1460,"Lvl 04",[Total Days] Between 1461 And 1825,"Lvl 05",[Total Days]>1825, "Lvl 06", [Total Days]=0,Null)
The function's results gives me 1522 days and according to my code the level is "05" (5 yrs). What am I doing wrong? Should I use a different function to calculate the days, so in this case would be 4 yrs and not 5?
Please help!
thanks,
Pablo:confused:
i want to calculate a date
Code:
Dim enddate As Date
Dim startdate As Date
Dim running_time As Double
startdate = Me.startdate
running_time = Me.running_time
endate = stardate + running time
Me.enddate = enddate
The running time is in year.If i calcluate right now, the System things the running time is in days. How can i say that the running time is in years.
I have a library system and i need to work out the number of days from the date the book was due and the current date. Could anyone help me.
View 2 Replies View RelatedCalculating dates between TWO date ranges:
I've read alot of good things on here regarding the DateDiff function and some other things that have helped me out finding the difference between two dates within the same entry on a table. I have a question regarding finding the difference in days between two entries within the same table.
Example:
test table:
Date ID # Name
1/18/07 1234567 Smith
1/20/07 1234567 Smith
1/23/07 1234567 Smith
Is there a way to find the difference in days between the lowest and highest dates? I want to know how many days are between the first entry and the last entry? In this cae it would be five days. I can do it if the days are in the same entry with the datediff function, but having trouble finding it in this situation. Any pointers?
(Thanks for all the good information on this site, BTW. I've seen a few posts similar to this, but it didn't really answer my question)
Don
I have a form with two date fields: "Initiation_date", and "Correction_date". There is a third field called "Duration". I would like to store the values of all three fields in the corresponding table. "Duration" (Long Integer) would be the number of days from "Initiation_Date" to "Correction_Date" if Correction date is there, or to Date() if Correction date is Null.
I can use an unbound text box to get "Duration_cal" using the following as a control source:
=IIf(IsNull([VCor_date]),DateDiff("d",[VInit_date],Date()),DateDiff("d",[VInit_date],[VCor_date]))
But I cannot get that value (No.of Days) in to the "Duration" field. The reason I want to have "Duration" stored as a number is that it is being used in a query for another calculation (total days used for selected records).
Is there a simple way to accomplish this?
Thanks
I currently have a table that has patient ID (unique record (SSN)) and a date of visit field. The SSN may have numerous rows with different dates of visits. What I am trying to do is a DateDiff function that will retrieve records that are 48 hours apart or 2 days apart. We are tracking patients that return within 48 hours of a previous visit.
View 10 Replies View RelatedI am tring to add a number of years to a dob. I'm doing this by adding my
date+years*365.26 I get a string of numbers. I then convert the number in
the next column to actual date again. I'm getting the correct date. I've tried unsuccesfully to use the dateadd function.Now I want my criteria on that column to allow me to return only date in a given to from period of my choosing. I want to determine the date range each time I run the query.
An example would be:
DOB age date I need to follow up
5/15/1935 + 75 = 5/15/2005
Then I request dates between 4/1/05 and 5/30/05 and this date is returned.
I've tried the between_and functions but nothing is returned. I'm guessing it's because the column is still a calculation and not a true date.
I've tried: Adding a column to my query to convert the number string back to
date format (criteria failed to return a result). Adding a second column
that ='s my converted number to date column (criteria failed to return a
result)
And I've tried the make table query and then run another query from the new table. I can't seemto make this happen. I think it's not recognizing my data as a date because it isn't returning any data.
Is ther an easier way to: Add a number of years to a date and have it return
a date and not the number string?
If not is there a better way to convert the string to a date?
Is there a way to get my criteria to recognize the date and return date
between my begin and end dates?
Thanks