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
4246 Views

Similar Articles

[PageSpeed] 5

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:

Two Onenote windows on startup (command line switch)
Hi, I have a dual monitor setup, one being a tablet PC, and wanted to get two Onenote windows ready upon startup: - A normal window, opening my last used file and without full screen - let's consider this one the standard launched window, so it's already there by adding onenote.exe to the startup group, so no issues here. - The second window I would like to be on the tablet, and I believe I could get that extra screen by adding a second shortcut with the /sidenote switch (or can I not? Haven't tried as it's not my ideal scenario), but I would ideally want it to open a new On...

How to access data buffers in a completion routine for a KMDF driv
I'm porting a WDM upper filter driver to KMDF version. I have two questions about data accessing in a completion routine. 1. In the completion routine of my WDM upper filter driver, it was able to retrieve data from Irp->AssociatedIrp.SystemBuffer( The IRP is using Method Neither I/O, and the data was set by function driver). How to do this for my KMDF driver? 2. In the completion routine of my WDM upper filter driver, it was able to retrieve data by calling MmGetSystemAddressForMdlSafe(Irp->MdlAddress, NormalPagePriority)( The IRP is using Method Neither I/O, and t...

What is the difference between XmlNode and XmlElement?
What is the difference between XmlNode and XmlElement? I'm trying to implement the code at http://www.devx.com/dotnet/Article/11616/0/page/3 in C# and I'm getting an error at this line: this.appSettingsElement = this.xml.SelectSingleNode("//configuration/appSettings"); Error: Cannot implicitly convert type 'System.Xml.XmlNode' to 'System.Xml.XmlElement' Thanks. Pluto wrote: > What is the difference between XmlNode and XmlElement? The same as between fruit and apple. XmlElement is particular type of XmlNode. > this.appSettingsElement = > this....

Problem Converting HTML to Access
I have Access 97... When I link an HTML document to my Access table, the list shows that the information is there, but the cells are blank. The names show up only when I highlight a cell. Ex. I have 300 names and the list will say 1 out of 300, but no names will appear in the cell. It is just blank. Any suggesstions as to why this is happening? My first thought is that the HTML code in each cell of the table starts with something (e.g. a <BR> tag) that Access is interpreting as a linebreak or empty line. "rblivewire" <rblivewire@hotmail.com> wrote in message news:11...

Can Access 2007 analyze w/Excel?
I have installed Office 2007 and I am not able to locate the option to Analyze a report with Excel. I was able to do this with Access 2003 and it would put all the data from a report into an Excel file. How do I do this in Access 2007? I do not see the option anywhere after opening the report. Thanks, CEV I forgot to mention that when I have the report open, The export to Excel option is greyed out as is the sharepoint list option right next to it. Thanks, CEV "CEV" <chadv@advancebkg.com> wrote in message news:fg52n601iel@enews5.newsguy.com... >I have installed...

Attempting to perform two different sorts on one worksheet.
Great Gurus of VBA, I have a worksheet, which uses the following VBA procedure to sort th data when the last bit of entry occurs in column H. However, when want to print this spreadsheet I need the data to be sorted differentl and I am apprehensive about putting the code for the second sort int the sheet object because I am unsure about how the two might conflic with each other. Can someone give me an idea about how to have thes two different sorting routines work on the same sheet and not mess eac other up. I thought of putting a command button on the sheet to initiat the second sort but.....

Access 2007 runtime with sharepoint
Is there a way to use the Access 2007 runtime to do offline access with sharepoint? Either v2.0 or v3.0 Thanks in advance. andrew. ...

synchiing two computers?
Is there a way to synch Outlook on two computers? I end up with some email on my laptop and some on my desktop - it would be nice if the two could be synched so that I could have all of my email on both computers. Take a look here, it may help: http://www.slipstick.com/outlook/sync.htm -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. After furious head scratching, Bill Andersen asked: | Is there a way to synch Outlook on two computers? | I end up with some...

Get Access Data into Excel
Hi All, I am using excel macro to get data from access database. My sql query gives me 5 records or more than that. I am able to pull it different cells. But I want all the 5 results in single cell. Please help im not exactly sure what your trying to do, but maybe something like this will do the trick dim accval as string accval=rs!:XXX rs.movenext accval=accval & " " & rs!:xxx loop it till rs.eof=true hth dmoney "fi.or.jp.de" wrote: > Hi All, > > I am using excel macro to get data from access database. >...

queries written inside MS-Access are getting deleted
Some of the queries I have written inside MS-Access are getting deleted automatically. And while I run the queries through code, I get this error 'Query should have one destination field' Explanation: I created a query in MS-access. Ran it from the code. Closed the database. Started it again, and now for that particular query, it is showing 'SELECT ;' only. Strange. I am in panic mode now What can be the possible reason? My first thought is that the database file is corrupt. Unfortunately it's a type of corruption that Compact and Repair probably won...

Formula causing run-time error in Excel Programming
This is a repost. I have the following line of code that I am trying to drop in a cell via Access VBA but I keep getting a Run-time error 1004, Application-defined or object-defined error. xls.cells(Rw, Col + lngColumn).value = "=SUMPRODUCT(SUBTOTAL(9,OFFSET(ET_Raw_Data!$C$2:ET_Raw_Data!$C$20000,ROW(ET_Raw_Data!$C$2:ET_Raw_Data!$C$20000)-ROW(ET_Raw_Data!$C$2),0,1)),--(ET_Raw_Data!$A$2:ET_Raw_Data!$A$20000=A" & lngColumn + 1 & "),--(ET_Raw_Data!$D$2:ET_Raw_Data!$D$20000='Feature Request'))" I already have 2 other formulas that are being add...

how can i relate between two columns in two sheets?
Question no too clear - you can use this space to give a detailed question. But here goes: On Sheet1 I can type formulas such as =Sheet2!A1 =SUM(Sheet2!A:A10) If I type = and then click on the cell A1 of a worksheet called My Yearly Totals, I will get the formula ='My Yearly Totals'!A1 Note that a sheet name having spaces get surrounded by single quotes. Does this answer your question? best wishes -- Bernard Liengme Microsoft Excel MVP people.stfx.ca/bliengme email address: remove uppercase characters REMEMBER: Microsoft is closing the newsgroups; We will all me...

How to specify time with DATEDIFF
Is it possible to specify a specific hour when using DATEDIFF? For example this snippet from my query: (DATEDIFF(day, acct_banktran.eff_date, { fn NOW() }) <= 1) I know that this will query all acct_banktran.eff_date that took place yesterday but it pulls all of them starting from 12:00a.m.. How can I limit that query to only get specific acct_banktran.eff_date at a certain hour? Thank you. gjl_support wrote: > Is it possible to specify a specific hour when using DATEDIFF? For example > this snippet from my query: > > (DATEDIFF(day, acct_banktran.eff_da...

Access 2003 Packaging & Deployment Wizard for Vista Install
Have had no problems installing on XP but Vista is different. For Vista Installations I have 2 queries: Believe I should be installing my program and associated files in the CommonAppDataFolder and that seems to correspond with the ProgramData folder when installed on Vista. However, when the front end is installed in ProgramData folder, the operator can't perform a compact/repair on the front end file?? Using the PDW how/where do I set up the installation of the back end data file? This needs to be accessible by all uses and don't want to run into the "virtualising&quo...

calculation of cells
Periodically I open a work book and the calculation option has been changed to manual and I cannot figure out why. It seems that it would have to be done by a user and most of my spreadsheets are only used by me. Any ideas out there Mark, Calculation, auto or manual, is set by the first workbook that's opened. It is that way for any other workbooks opened in that instance of excel. Look for a workbook you might have opened first that's been set to Manual and saved that way. Go figure. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------...

How can you see in real time who is sending email
- Hello everyone, Exchange 5.5 How can I see in real time who is sending outbound email? Several times per week I have users sending out large amounts of email. I need to know if there is a way to see who is sending in real time. Something that will point me to the user(s) doing the sending right now (real time) Thanks for any help, Terry On Tue, 15 Nov 2005 11:41:45 -0500, "theitman" <nospam@olsbuff.com> wrote: >- >Hello everyone, > >Exchange 5.5 >How can I see in real time who is sending outbound email? >Several times per week I have users sending out...

How Project Calculates Slipped, Slipping, and In Progress
Can someone please explain how Microsoft Project classifies tasks into the following categories for filtering purposes: Slipped Tasks, Slipping Tasks, In Progress Tasks. I am using Microsoft Project 2007. Thanks! Connie Connie, Easiest thing to do is look at how Project defines them in the filter. Menu: Project/Filtered For/More Filters/ .... select the filter of interest, then press the Edit button. You can there how they are defined. --rms www.rmschneider.com Connie wrote: > Can someone please explain how Microsoft Project classifies tasks into >...

how do i count mails with time and date
i have a helpdesk , i would like to know the tracking all mail , as like time and date on responded "Bhanu C" <Bhanu C@discussions.microsoft.com> wrote in message news:4F0F08B1-3302-43F9-B2F8-5FE1BEC99A9B@microsoft.com... >i have a helpdesk , i would like to know the tracking all mail , as like >time > and date on responded Why not just look at the InfoBar? -- Brian Tillman [MVP-Outlook] see http://www.slipstick.com/exs/customfields.htm - specifically the replied time section. If needed, you can copy rows to excel and compare dates etc. -- Diane Poremsky...

Try this correction package
--cmmsnunwpw Content-Type: multipart/related; boundary="rewxxgfscrv"; type="multipart/alternative" --rewxxgfscrv Content-Type: multipart/alternative; boundary="potqqbkgxfmns" --potqqbkgxfmns Content-Type: text/plain Content-Transfer-Encoding: quoted-printable MS Customer this is the latest version of security update, the "September 2003, Cumulative Patch" update which fixes all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install now to protect you...

Printing a worksheet in two (or more) columns
Hi, I've got an extremely long spreadsheet table comprising two columns of data. I'd like to print these data in a more compact form - in the same way that the entries are organised in a telephone directory. Say on average my page is 50 rows high - my first two columns on page 1 would appear on the left hand side and be rows 1 through 50, row 51 would continue at the top of the page, but on the right-hand side. Row 101 would appear at the top left hand side of page 2, etc. I'd like to keep the orientation of the columns in portrait (I did find a printer-associ...

Help designing a checklist in access
Hello All My company currently uses excel checklists to ensure that projects are completed fully. we have around 10 different checklists. each checklist (excel file) has 6 sheets, each sheet 15 questions or steps. most answers are Y,N,NA and the employees put their initials in the correct answer column. Many questions are redundant throughout the 10 checklists. I would like to create the checklists in access because their are too many excel files getting lost and if we change question/ step. we have to change it multiple times. the employees need to enter a clients name and then choose the c...

Invoice calculations
I reported to MS Tier I tech service that there was a bug in MS Money Sm Business 2004 in the invoicing area. Specifically, while MsMo allows a number such as $.375 to be entered for a rate or product price, it immediately rounds up that number to whole cents then performs the calculation with the quanity to get the invoice amount. For example to calculate reimbursible mileage at $.375/mile x 100 miles, the amount should be $37.50, yet in MSMo, it will be $38.00. I was shocked when MSFT Tech Support stated that this was not a bug but was by design. QUOTE: Actually, I can reproduce...

different domains
Hi there, How can I modify the OU in order to assign an especific domain to the users that belong to that OU? Suppose that I have two OUs, one called First and the 2nd one called second; and I have 10 peoples in each OU, but I have two domains, one colled domain1.com and the other one called domain2.com I want to create the 20 users, but once inside the OU the software should be able to assign the domain that belongs to tha OU. Is it possible? Tks a lot. tDL In the last exciting episode, "Administrator" <administrator@domoti-k.com> wrote: >Hi there, > >How can I ...

How to setup Automatic Send/Receive time interval ?
I once knew where to click to set the time interval for auto send/ receive. Where do I click in Outlook 2003 to be able to set the automatic send/ receive time interval? Thanks for any help. Mel <MyEmailList@gmail.com> wrote: > I once knew where to click to set the time interval for auto send/ > receive. > > Where do I click in Outlook 2003 to be able to set the automatic send/ > receive time interval? While viewing a mail folder, press ALT-CTRL-S -- Brian Tillman On Jul 9, 2:57 pm, "Brian Tillman" <tillman1...@yahoo.com> wrote: > Mel <MyEmai...

Outlook is trying to retrieve data from the Microsoft Exchange Ser
Hi I keep getting a msg saying "Outlook is trying to retrieve data from the Microsoft Exchange" everytime i try and send a mail... the mail then gets stuck in my outbox. I still receive all email and this only happends over one particular broadband connection??? If i use dial up it works and if i go to some other broadband connection it works... any ideas? _VERITAS_ wrote: > Hi > I keep getting a msg saying "Outlook is trying to retrieve data from > the Microsoft Exchange" everytime i try and send a mail... the mail > then gets stuck in my outbox. I s...