Trying to calculate difference between two times in an access repo

I am trying to calculate the difference between two times in an access report 
and don't know what the correct expression is.  
My report has a column of "Start" and "End" that has the start time of the 
project and the end time of the project.  I need the difference between the 2 
so I created an expression for "Total production time" - 
=timediff("h",[end],[start]) but a box keeps appearing when I try to view the 
report which means I have an error and I don't know where I went wrong
0
Utf
6/6/2010 6:26:46 PM
access.reports 4434 articles. 0 followers. Follow

2 Replies
3381 Views

Similar Articles

[PageSpeed] 37

Try the DateDiff() function. "h" might not produce the desired results. I 
would try:
   =DateDiff("n",[end],[start])/60
n is for minutes.

-- 
Duane Hookom
Microsoft Access MVP

NOTE: These public News Groups are ending June 1st. Consider asking 
questions at http://social.answers.microsoft.com/Forums/en-US/addbuz/threads?


"jackie" wrote:

> I am trying to calculate the difference between two times in an access report 
> and don't know what the correct expression is.  
> My report has a column of "Start" and "End" that has the start time of the 
> project and the end time of the project.  I need the difference between the 2 
> so I created an expression for "Total production time" - 
> =timediff("h",[end],[start]) but a box keeps appearing when I try to view the 
> report which means I have an error and I don't know where I went wrong
0
Utf
6/6/2010 7:39:45 PM
You might also like to try the following function, which returns the time
difference as hours:minutes:seconds:

Public Function TimeDuration(dtmFrom As Date, dtmTo As Date, _
            Optional blnShowdays As Boolean = False) As String
            
    ' Returns duration between two date/time values
    ' in format hh:nn:ss, or d:hh:nn:ss if optional
    ' blnShowDays argument is True.
    
    ' If 'time values' only passed into function and
    ' 'from' time if later than 'to' time, assumed that
    ' this relates to a 'shift' spanning midnight and one day
    ' is therefore subtracted from 'from' time

    Dim dtmTime As Date
    Dim lngDays As Long
    Dim strDays As String
    Dim strHours As String
    
    ' subtract one day from 'from' time if later than 'to' time
    If dtmTo < dtmFrom Then
        If Int(dtmFrom) + Int(dtmTo) = 0 Then
            dtmFrom = dtmFrom - 1
        End If
    End If
    
    ' get duration as date time data type
    dtmTime = dtmTo - dtmFrom
    
    ' get whole days
    lngDays = Int(dtmTime)
    strDays = CStr(lngDays)
    ' get hours
    strHours = Format(dtmTime, "hh")
    
    If blnShowdays Then
        TimeDuration = lngDays & ":" & strHours & Format(dtmTime, ":nn:ss")
    Else
        TimeDuration = Format((Val(strDays) * 24) + Val(strHours), "00") & _
            Format(dtmTime, ":nn:ss")
    End If
    
End Function

Add it to a standard module and call it in your report with:

=TimeDuration([start],[end])

The principle thing about this function is that it will also correctly
compute the difference between times which span midnight where these have
been entered without any dates, provided that the times are less than 24
hours apart.  Normally this would give the wrong answer as 8.00 PM is later
than 4.00 AM for instance.

For long durations between date/time values you can opt to return  days:hours:
minutes:seconds if you wish by including the third optional argument:

=TimeDuration([start],[end],True)

Note that the function returns a string.  If you need to aggregate a set of
time differences you can't sum the return value of the function for instance,
but you can return the difference as date/time value with this function:

Public Function TimeDurationAsDate(dtmFrom As Date, dtmTo As Date) As Date
            
    ' Returns duration between two date/time values
    ' as a date/time value
    
    ' If 'time values' only passed into function and
    ' 'from' time if later than 'to' time, assumed that
    ' this relates to a 'shift' spanning midnight and one day
    ' is therefore subtracted from 'from' time

    ' subtract one day from 'from' time if later than 'to' time
    If dtmTo < dtmFrom Then
        If Int(dtmFrom) + Int(dtmTo) = 0 Then
            dtmFrom = dtmFrom - 1
        End If
    End If
    
    ' get duration as date time data type
    TimeDurationAsDate = dtmTo - dtmFrom
    
End Function

So you can sum the durations, in a group footer in a report for instance:

=Sum(TimeDurationAsDate([start],[end]))

Summing date/time values will not give you a result in hours;minutes;seconds
format, but as a decimal number of days, however, so you need to convert the
result with another function:

Public Function TimeToString(dtmTime As Date, _
    Optional blnShowdays As Boolean = False) As String
    
    Dim lngDays As Long
    Dim strDays As String
    Dim strHours As String

    ' get whole days
    lngDays = Int(dtmTime)
    strDays = CStr(lngDays)
    ' get hours
    strHours = Format(dtmTime, "hh")
    
    If blnShowdays Then
        TimeToString = lngDays & ":" & strHours & Format(dtmTime, ":nn:ss")
    Else
        TimeToString = Format((Val(strDays) * 24) + Val(strHours), "00") & _
            Format(dtmTime, ":nn:ss")
    End If

End Function

So for a control to sum the differences and return the result as hours:
minutes;seconds you'd use:

=TimeToString(Sum(TimeDurationAsDate([start],[end])))

Ken Sheridan
Stafford, England

jackie wrote:
>I am trying to calculate the difference between two times in an access report 
>and don't know what the correct expression is.  
>My report has a column of "Start" and "End" that has the start time of the 
>project and the end time of the project.  I need the difference between the 2 
>so I created an expression for "Total production time" - 
>=timediff("h",[end],[start]) but a box keeps appearing when I try to view the 
>report which means I have an error and I don't know where I went wrong

-- 
Message posted via http://www.accessmonster.com

0
KenSheridan
6/6/2010 11:30:25 PM
Reply:

Similar Artilces:

Finding difference in Month Values
I'm trying to extract differences in month values - eg Values >1 (january) and <4(april) return 11,12 ,2 & 3. How can I get results 2 & 3 or february and march? Thanks to any one that can help with this. Margaret You need to give us more infomation.... post the SQL statement of the query that you're trying to run. I'm guessing, based on the values being selected, that you're Values field/variable is a text data type, which will cause the problem you're seeing. In text data, 11 and 12 are less than 4 because the first "digit" (actually a te...

Two display monitors....
I have two monitors attached to two video adapters in the same PC. 1. How can I know from my Visual C++ program how many monitors do I actually have (I want the program to behave differently if only one monitor is available)? 2. How can I setup de resolution for both adapters/monitors from the C++ program? You can use EnumDisplayDevices to get information about all available display devices attached to your computer. Then you can use ChangeDisplaySettingsEx to change the resolution on a specific device. I personally haven't done this before, but I thing it will work. Ali R. "J...

trying this out
This is a test posting. On Tue, 13 Nov 2007 16:06:51 +0800, "hmmmm" <bobo123@dotdot.dyndns.org> wrote: >This is a test posting. > Please use microsoft.public.test or microsoft.public.test.here for your testing. This is a working newsgroup. If you have questions about an Access database, please post them. John W. Vinson [MVP] ...

Run time error '340'
hey everyone, i am trying to display this querie in POS. It runs fine the only problem is when i close POS i see an alert "run time error '340' control array element '5' dosent exist can anybody please see the code and tell me what i am doing wrong var sql = "SELECT Item.Description, Item.itemLookupCode FROM Item WHERE Item.Description LIKE '" + description + "%'"; var rs = qsRules.OpenRecordSet(sql, true); while (!rs.EOF) { document.writeln ( rs("Description").value); document.writeln (" "); document.writeln ( rs(...

I am trying to round up to the nearest 25 cents
I am trying to round a money value to the nearsest 25 cents and it keeps rounding to the nearest dollar HELP Try =CEILING(A1, 0.25) In article <2DB8F007-1E27-4A48-923B-C7D4D0238B5E@microsoft.com>, sypher <sypher@discussions.microsoft.com> wrote: > I am trying to round a money value to the nearsest 25 cents and it keeps > rounding to the nearest dollar HELP =ROUND(A1*4,0)/4 -- HTH Bob Phillips "sypher" <sypher@discussions.microsoft.com> wrote in message news:2DB8F007-1E27-4A48-923B-C7D4D0238B5E@microsoft.com... > I am trying to round a mone...

Measure on the date different
Hi, I had a huge file of aroiund 10 000 line. This 10 000 line only consist of 500 items. I wish to have an analyise a information. Example in the file it have items, order date, recept date and qty. Item A order 3 time on 1 Jan but recept 1 time on 1 Feb and 2 time on 15 Feb. Item A order 2 time on 1 Feb but recept i time on 1 Mar and 1 time on 15 Mar etc for Item B...... Ans Item A 2 time take 30 days and 3 time 45 days. The numbers of days can be range 25 to 30 act as 30 and 40 to 50 can act as 45. etc for Item B ....... Have you tried a pivot table? -- HTH Bo...

Trying to clear up Received but not invoiced report
I am trying to clean up the received not invoiced items. The problem began with entering a purchase order and then not matching the invoice to the items received agaisnt the purchase order. SO we thought that doing a returned transaction entery would solve the problem. The only thing is now the return shows up on our Received/ Not invoiced report. And the report is becoming a huge mess and difficult to use. What is the best way to clear up items that have been received but were not matched and already paid through the invoice entery? There is a knowledgebase article on this very thing av...

Accumulate weekly time to total time in Excel.
I need to maintain project total time by incrementing the value based on people entering amount of time that a project was worked on. The weekly value of entered time will not be stored on a weekly basis. I would like to have the value for total project time increment its value based on the entered value by the person for the time worked in the week. ...

send/receive (2nd try)
New computer. Microsoft XP Home. Microsoft Office XP Small Business 2002. Earthlink dial-up account. Two e- mail accounts. After opening Outlook, 1st send/receive (option 3, all accounts) usually prompts for both passwords, downloads messages, all good. Next time, though, no prompt for account 2, error cannot connect to server, account 1 usually gets messages. Every five minute send/receive option eventually degenerates to cannot connect to server errors for both accounts. Meanwhile internet browsing slows to a crawl. Any ideas? ...

Automatically calculating...
Hi, Im trying to make a spreadsheet calculate Columns to show difference (in the context of points gained and points lost), a column that shows league points (2 if the team wins, 1 if they draw, and none if they loose), as well as a column that will show a comment saying whether the team has won lost or drawn. Thanking you in advance, Andy Andy A B C D E F Home Away H Goals A Goals Points Comment Lpool Arsenal 2 2 1 Draw Formula for column E =IF(C16>D16,2,IF(C16<D16,0,1)) Formula for column F =IF(E16=2,"Win",IF(E16=1,"Draw",&...

Queries calculation
I have a table called “openbills”. I have a query called “billsopenvalue” In the table I have a field where I insert the amount a customer paid – this can be as well a part payment of the bill. The query calculates how much is still pending to pay. Now the problem: Ie: the total to be paid amount is 1000. The customer paid 100. = Result of query 900 Now the customer paid another 100 – normally the result of the query should be 800; but it is again 900 because the resting value is calculated: total to be paid – paid amount = resting amount. How can I fix after each calculation the restin...

time compare
i need an example of how to code a time comparison. i need to check to see if a value in a date/time column is greater than 10 minutes from the current time. i appreciate your help. thanks, mcnewsxp On Fri, 8 Oct 2010 09:25:43 -0400, "Michael Courter" <mcourter@mindspring.com> wrote: >i need an example of how to code a time comparison. >i need to check to see if a value in a date/time column is greater than 10 >minutes from the current time. >i appreciate your help. >thanks, >mcnewsxp > Difftime() -ralph Michael Courter wrot...

Share Money File on Two Computers
I just got a laptop and have money on my desktop and laptop and have a wireless network. My question is can I have my money file work between the two computers. I want to be able to use the file on either computer. I also have a usb flash drive if that would work better. Thanks, Jeremy cy_bear@msn.com In microsoft.public.money, Jeremy wrote: >I just got a laptop and have money on my desktop and >laptop and have a wireless network. My question is can I >have my money file work between the two computers. See http://www.bollar.org/msmoney/ Q5 for information. > I >wa...

Changing 1 word to another word on a different page
I have a work sheet that if an answer in a cell on page 2 is "Yes" or "No", I need it to display the opposite in a cell on page 1 In a1 in sheet1 put =IF(Sheet2!A1="yes","no","") -- Russell Dawson Excel student "Circuitman57" wrote: > I have a work sheet that if an answer in a cell on page 2 is "Yes" or "No", > I need it to display the opposite in a cell on page 1 Missed a bit =IF(Sheet2!A1="yes","no",IF(Sheet2!A1="no","yes","")) -- ...

Using Lookup Query with Calculated Value in Table ComboBox
Hello all, This question takes a complicated setup, but I'll try to keep it brief. I have a database with a main table (tblMain) and a lookup table (tblLookup). tblMain has an integer field (LookupMe) that is related to the ID field in tblLookup. I set up LookupMe to use a ComboBox input display control that uses a query as its source. The query contains two columns: tblLookup.ID and a calculated value, say "[First Name] & " " & [Last Name]". Back in tblMain, the LookupMe field has the following properties: Bound Column: 1 Column Count: 2...

Join same field on two tables
Forgive if this is really stupid. This is what I am dealing with in terms as simple as I can come up with: I have one table with multiple records (country names) and multiple fields (over 20). I have a second table with that contains only a limited number of records (all of which are also represented in the other table) with only two fields (also represented in the other table). I need to know if it is possible to make the information from the second table "join" with the first table and display the data from both tables in one column. Does that make any sense? You can combine da...

How to send email after entry from data access page
I hope this is an easy one, but i am not a programmer. I got volunteered for this because i built an inventory database. Access 2003I have a simple referral table that i have linked to an "Input" page for people to enter records (including a drop down list of email addresses). And an "Output" page where pepole can come and edit. That part seems to be working fine.I need to have an email sent out when a new record is posted. Ideally, i would like it to be sent out based on who is selected in that email list, but i would settle for anything at this point.Thank you in adv...

How do I cancel a msg Outlook keeps trying to send unsuccessfully?
How do I cancel an "in process" email Outlook keeps trying to send unsuccessfully due to a huge email message I tried to send with way too many attachments? It's stuck in "send" mode, and as a result, I can't send any emails because Outlook cannot get past trying to send this message. How do I cancel a pending outbox transmission while it's trying to send? I've tried everything I can think of to no avail. Please help! Angelczech wrote on Fri, 12 March 2010 07:32 > How do I cancel an "in process" email Outlook keeps trying to send ...

Two sided business cards
Hi all I am just designing a business card. I have everything set up perfectly but I want to print two sided cards.. Is there a trick to doing it? Thanks TM After managing to set up OE-QuoteFix on his new PC, Ed reads a message from TM <marshalls6710@rogers.com>... > I am just designing a business card. I have everything set up > perfectly but I want to print two sided cards.. Is there a trick to > doing it? If your cardstock is symmetrical, there should be no problem - just turn the stock over after side 1 has printed, and print side 2. Are you having trouble? -- Ed Be...

Number of different email accounts
Version: 2008 Email Client: pop Hi, is there a limit on the number of different email accounts you can set up in Entourage? I have set up three (sucessfully, I think!) but cannot seem to set up any more. <br><br>Thanks for your advice. <br> PS, I know 'sucessfully' is spelled wrong but can't find the spell check!! On 2010-03-07 05:34:15 -0800, AlmostThere@officeformac.com said: > Version: 2008 Email Client: pop Hi, is there a limit on the number of > different email accounts you can set up in Entourage? I have set up > three (sucessfully, I ...

trying to insert a 0 in front of zipcode
Hello, I do not use excel at all so i have no clue! I have a list of daycares that i want to create mailing labels with...about 700 of them but the problem is my zipcode column there needs to be a zero in front of all 700 zipcodes how to i insert a zero in front of them without having to type it into each box? the other question is can i print labels directly from excel or do i have to go thru ms word? thanks Greg One of these 1) format the cells with custom format 00000 2) format the cells with Special / Zip Codes 3) format the cells as Text 4) begin all zip codes with an sin...

Text Box Calculation Formula
SpreadCred InsideSpread LeftMain RightMain I have a text box (SpreadCred) that needs to be calculated/populated by a combination of factors. 1. SpreadCred should populate off of the text box - InsideSpread. 2. The value in SpreadCred can not be greater than text box - RightMain nor text box - LeftMain. If it is bigger than either number, it will max out at the larger of the two. Thanks for your time. How this is done depends a little on whether the value will respond to user input or to previously-entered data. This could be done with a nested IIf, but is really a ...

How to set time within macro?
Does anyone have any suggestions on how to set time for processing the code? I would like to add a condition that allow process the code before 4 pm, Does anyone have any suggestions on how to do it? Thanks in advance for any suggestions Eric If FileDateTime(PathFileName) > Date + TimeSerial(8, 0, 0) Then {It is working!} I would like to add another condition that allow process the code before 4 pm, which I tried and show below, but it does not work. If FileDateTime(PathFileName) > Date + TimeSerial(8, 0, 0) Or Now < TimeSerial(16, 0, 0) Then {It does not wor...

Punch time query to calculate total work time
I have a table containing two fields; Employee ID and Time stamp, respectively. Let's say the table contains the following values: Emp ID Time Stamp ------- ------------ 1001 1/1/2010 09:00 1001 1/1/2010 12:00 1001 1/1/2010 13:30 1001 1/1/2010 17:00 The data reflects that employee #1001 has clocked-in at 09:00 am and clocked-out at 12:00pm. The employee has clocked-in again at 1:30 pm and finally clocked out at 5:00 pm. Since the data was generated using Employee ID card, there is no separate time-in and time out field. How can I qu...

time sheet
Does anyone know how to set up an employee time sheet in Excel 97? I need to keep track of my hours at work and I can't figure out how to format time. Thanks. Jen, Not sure what you're looking for from your question. There are soooo many ways to do this. Anyway, for some coding and examples, try here: http://www.cpearson.com/excel/overtime.htm http://j-walk.com/ss/excel/files/timesht.htm http://www.mvps.org/dmcritchie/excel/datetime.htm John Jen wrote: > Does anyone know how to set up an employee time sheet in > Excel 97? I need to keep track of my hours at work and I &...