Calculating the differance between 2 time fields

I have 2 time fields in a query that i want to know the differance of. I want 
the result to formatted "hh,mm" in the field. Can this happen in a query?
0
Utf
2/29/2008 4:35:03 PM
access 16762 articles. 3 followers. Follow

4 Replies
1006 Views

Similar Articles

[PageSpeed] 9

You can use

DateDiff("n", [Time1], [Time2])\60 & ":" & Format(DateDiff("n", [Time1], 
[Time2]) Mod 60, "00")

Note that this returns a string, so you will not be able to do arithmetic on 
the result.

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"JWG" <JWG@discussions.microsoft.com> wrote in message 
news:4F61E1A2-425E-4630-ACBC-82D25FF181C8@microsoft.com...
>I have 2 time fields in a query that i want to know the differance of. I 
>want
> the result to formatted "hh,mm" in the field. Can this happen in a query? 

0
Douglas
2/29/2008 4:48:05 PM
Ok that will help, but what happens if the time in is like 23:00 and the time 
out is 01:00 the next morning. I get -23:00 as the answer not 2 which is the 
true time on the job.

"Douglas J. Steele" wrote:

> You can use
> 
> DateDiff("n", [Time1], [Time2])\60 & ":" & Format(DateDiff("n", [Time1], 
> [Time2]) Mod 60, "00")
> 
> Note that this returns a string, so you will not be able to do arithmetic on 
> the result.
> 
> -- 
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
> 
> 
> "JWG" <JWG@discussions.microsoft.com> wrote in message 
> news:4F61E1A2-425E-4630-ACBC-82D25FF181C8@microsoft.com...
> >I have 2 time fields in a query that i want to know the differance of. I 
> >want
> > the result to formatted "hh,mm" in the field. Can this happen in a query? 
> 
> 
0
Utf
2/29/2008 7:02:05 PM
Realistically, your fields should contain both Date and Time. Access doesn't 
really support time-only. (The Date data type is an 8 byte floating point 
number, where the integer portion represents the date as the number of days 
relative to 30 Dec, 1899, and the decimal portion represents the time as a 
fraction of a day. In other words, if you're only storing 23:00 and 1:00, 
you're actually storing 23:00 on 30 Dec, 1899 and 1:00 on 30 Dec, 1899)

If it's too late to go back and do it correctly, see 
http://www.mvps.org/access/datetime/date0008.htm at "The Access Web".

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"JWG" <JWG@discussions.microsoft.com> wrote in message 
news:4406BE3C-5788-4BA2-BC7F-9056B6E0D803@microsoft.com...
> Ok that will help, but what happens if the time in is like 23:00 and the 
> time
> out is 01:00 the next morning. I get -23:00 as the answer not 2 which is 
> the
> true time on the job.
>
> "Douglas J. Steele" wrote:
>
>> You can use
>>
>> DateDiff("n", [Time1], [Time2])\60 & ":" & Format(DateDiff("n", [Time1],
>> [Time2]) Mod 60, "00")
>>
>> Note that this returns a string, so you will not be able to do arithmetic 
>> on
>> the result.
>>
>> -- 
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no e-mails, please!)
>>
>>
>> "JWG" <JWG@discussions.microsoft.com> wrote in message
>> news:4F61E1A2-425E-4630-ACBC-82D25FF181C8@microsoft.com...
>> >I have 2 time fields in a query that i want to know the differance of. I
>> >want
>> > the result to formatted "hh,mm" in the field. Can this happen in a 
>> > query?
>>
>> 

0
Douglas
3/1/2008 1:43:36 AM
On Fri, 29 Feb 2008 11:02:05 -0800, JWG <JWG@discussions.microsoft.com> wrote:

>Ok that will help, but what happens if the time in is like 23:00 and the time 
>out is 01:00 the next morning. I get -23:00 as the answer not 2 which is the 
>true time on the job.

I absolutely agree with Douglas that you should store the date and time. I
doubt it would arise in a workshift situation, but you could span TWO
midnights - from 23:00 to 1:00 might be two hours or it might be 26, and if
the time is all you're storing there's no way to tell.

That said... you can get the positive time if you assume that the total span
will never exceed 24 hours:

(DateDiff("n", [start], [end])+IIF([start] > [end], 1440, 0) \ 60 &
Format(DateDiff("n", [start], [end]) MOD 60, ":00")

-- 
             John W. Vinson [MVP]
0
John
3/1/2008 5:26:06 PM
Reply:

Similar Artilces:

Converting to PDF #2
I lose my hyperlinks to email addresses when I convert from Publisher 2002 to a .PDF in Adobe Acrobat 6.0 Professional. Do I need to upgrade to Publisher 2003 or can this be fixed? While in a state of ecstasy after repairing his laptop, Ed sees a message from Banks <Banks@discussions.microsoft.com>. On it is written: > I lose my hyperlinks to email addresses when I convert from Publisher > 2002 to a .PDF in Adobe Acrobat 6.0 Professional. Publisher sends printable information to the printer driver. As you cannot click a hyperlink on a printed piece of paper, Publisher doesn&#...

Problems with public folders #2
I am having two problems which may or may not be related. 1) In System Manager when I try to view or work with the public folder tree, I get the following error "The token supplied to the function is invalid", ID no:80090308. I cannot see any public folders or manage them at all after that. 2) Whenever anyone clicks on send/receive in outlook 2003, they get the following error "Task 'Microsoft Exchange Server' reported error (0x8004010F):'The operation failed. An object could not be found.'" Does anyone have an idea of what the problem is for either...

How do I add vertical lines to separate columns in Outlook 2003 #2
In Outlook 2000, all columns were separated by a gray vertical line. I can't seem to set this up in Outlook 2003. Any help would be appreciated ARe you using Word as the message editor? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, mschwartz asked: | In Outlook 2000, all columns were separated by a gray | vertical line. I can't seem to set this up in O...

Swedish characters in the Display name #2
I have a problem with Swedish characters in the senders' names on outgoing e-mail. Previously we have formatted the display names like: Last name, First name This is causing problems for some external recipients, since their e-mail program reads this as two addresses (Last name as one name and First name as another) I tried changing the display name for one user to "First name Last name" instead, but then the Swedish characters are not shown when he/she sends an external e-mail. Example: "Åke Jönsson" becomes "Ake Jonsson" or "Åsa H...

Print 2 receipts when selling item with rebate?
Is there any way to program rms to print multiple receipts on particullar items, for example I sell an automatic pool cleaner that has two differant mail in rebates and we always supply the customer with duplicate receipts Victor Out of the box, no. The easiest way is to train cashiers to use the Reprint function when they need a second copy. The other option is to configure the receipts to print 2 copies every time, but that's probably a waste of paper. I'm pretty sure this could be accomplished as a customization using the HTML status bar and QSBridge, but you would need a ...

Calculated fields in Pivot Tables
Is it possible to use an "if" formula when creating a calculated field? I have an existing field in my pivot table called commissions and I want to create a new field that will give me a 1 if for each row if commissions are over 4 and a 0 if they are under 4. Is this possible? What is the formula? It seems ok Try something like : =IF(Commissions>=4,1,0) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "lj" <lj@spu.edu> wrote in message news:1132008509.143327.194520@g47g2000cwa.googlegroups.com... > Is it possible to u...

2007 Schema Prep #2
I extended my AD last night for 2007 and have a few questions to verify it was successful: I have a root domain (domain.local) that houses my schema master and a child domain (domain.net) that has Exchange 2003. I ran setup /prepareAD then ran setup /preparedomain: domain.net and both said they completed successfully. However, I was expecting to see the new OU (Microsoft Exchange Security Groups) in my child domain. It is present in my root domain. Is that all correct? In my child domain I now have a new global security group call Exchange Install Domain Servers. Last thing, the D...

Formula query #2
I am trying to identify a formula which will add and then display th number of times a number appears in a range. For example, the range looks like this .......A...........B............C 1...1110......1110.......1114 2...1110..... 1111.......1111 3...1111......1112.......1110 4...1111......1113.......1111 The numbers which appear in the applicable range e.g. 1110, could b any number between 1110 and 9999 and as such I cannot simply predefin which number to look for. I am hoping the output could like as follows: .......A........B 1....1110....4 2....1111....5 3....1112....1 4.....

if formula #2
i have this formula in a cell D3 =IF(A1="F",B1&B2) how can i make it multiple for example i want to add this IF(a2="G",B1&B3) in D3 also. thanks Either - =IF(AND(A1="F",A2="G"),B1&B3,) =IF(OR(A1="F",A2="G"),B1&B3,) Regards. Bill Ridgeway Computer Solutions "jaypee" <jaypee2cool@yahoo.com> wrote in message news:O3NhGKIhGHA.1272@TK2MSFTNGP03.phx.gbl... >i have this formula in a cell D3 =IF(A1="F",B1&B2) > how can i make it multiple for example i want to add this IF(a2="G&q...

Set field focus in a subroutine
In Access 2003 (Windows XP) I am passing the value of a field in a textbox on a form to a subroutine to validate that the date value is within a range. If the date is out of range I would like to set the focus of the field on the form and display an error message. When I pass in the field to the subroutine, I get a compile error "Invalid qualifier" when I try to set focus to the date field. How can I set the focus to the field within the subroutine. Here is the subrotuine code: Public Sub CheckDates(date1 As Date) If Not IsNull(date1) And date1 < [Forms]![frmMR...

Select Rows dependant on certain field data
How can I select certain rows in a spreadsheet based upon the text foun in one column? To explain: All rows have a column that contains tex "immediate" or "ongoing" or "closed". How can I select only the row that contain the word "immediate" in that column? When I say select a mean select as in ready to 'copy' or 'cut' tha data ready to be pasted elsewhere. I know what I mean :confused -- Madd ----------------------------------------------------------------------- Maddy's Profile: http://www.excelforum.com/member.php?action=getinf...

Reminder is not work in default folder #2
I used as follow coding to send email to outlook client(outlook 2000): ''''''''''''''''''''''''''''''=AD'''''''''''''''' Dim ll As New CDO.Message Dim bb As New CDO.Configuration bb.Fields(cdoSMTPServer) =3D "nc80012" bb.Fields(cdoSendUsingMethod) =3D cdoSendUsingPort bb.Fields.Update Set ll.Configuration =3D bb ll.Fields.Append "urn:schemas:mailheader:x-mess=ADage-flag", adBSTR _ ...

POP3 & IMAP4 services #2
Hi there, I got problems on starting POP3 & IMAP4 services. Recently they cannot be start after my exchange server restarts. At the beginning, POP3 cannot be start automatically, later it's IMAP4. After I can start them manually after the server has been completely started, I check event viewer, I found the server did try to start IMAP4, but got error "An error occurred while starting the Microsoft Exchange IMAP4 Service: server instance number 1 failed to start with error 0x80040a01." At the same time, an event also there, "Microsoft Exchange IMAP4 Service (Build...

allow user to highlight field on form
I have a database which records info about disabled people. One form is used to input comments about the the person and has several memo fields. The user wants to be able to highlight a field if the information is important and needs to be highlighted to other users of the system. I cannot use conditional formatting because the decision to highlight the field is made by the user depending on different circumstances. Any help would be appreciated On Mon, 3 May 2010 16:52:01 -0700, aussiebob wrote: > I have a database which records info about disabled people. One form is used...

2 accounts being sent under one address
I currently have two accounts in my Outlook 2000. xxx@provider.net & PPP@provider.net. The PPP@provider.net is the primary account. When sending an email from the xxx@provider.net, it still shows as the PPP@provider.net address, so in turn I get any replies to the xxx@provider.net back to the PPP@provider.net. Is there any way to make the reply address or the address of sender shown to the xxx@provider.net account? You said you had 2 accounts. You then listed 2 email addresses. Those are not the same thing. Clarify your post. -- Russ Valentine [MVP-Outlook] "jeepstr9" ...

Compare 2 Worksheets Create a 3rd depending on results
I have a unique problem that none of the other posts or shareware seems to solve. I have 2 worksheets (orig.xls and new.xls) that are 15 columns wide (to the "O"). The data is just numbers but the second column either has the words: "new" or "cancel" which is important as you will see. I need to compare the two worksheets and create a third worksheet (update.xls) depending on the three possible results: 1) If a row is removed in the new.xls file = copy the row from the orig.xls file and make the 2nd column "Cancel" 2) If a row is added to the new.xls...

Printing Externally #2
That works great...except it word brings up a message box stating tha Word is currently printing, if you exit, you will cancel you print.....and if i click no, so i dont close word, it still doesnt wan to print...any suggestions???Thank -- tess45 ----------------------------------------------------------------------- tess457's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1393 View this thread: http://www.excelforum.com/showthread.php?threadid=26662 There's a setting in MSWord that allows you to do background printing. I've turned this off--so Word do...

money 03 same funds in 2 accounts
I have 1 tax defered account and 1 non-tax defered account. They both have the same mutual fund in them. Money won't let me use the same symbol twice for 2 accounts. Any thoughts ? See http://umpmfaq.info/faqdb.php?q=59. "Fruehauf2000" <fruehauf2000@aol.com> wrote in message news:20041201213351.11419.00001421@mb-m29.aol.com... >I have 1 tax defered account and 1 non-tax defered account. They both have >the > same mutual fund in them. Money won't let me use the same symbol twice for > 2 > accounts. Any thoughts ? ...

OT: Trend Micro WFBS SP2 beta starting 2/15/10
Anyone interested in helping to improve the WFBS product should join the beta...and all future ones. https://www.trendbeta.com/index.php?get=356&content=554 Gregg Hill -- Gregg's pet peeves: First of all, what does a peeve look like, and why would anyone want one as a pet? Peeve #1: Apostrophes: when in doubt, leave them out! You will be correct more often than not. Its = Belonging to it. For example, "Look at the sky. Its color is blue." It's = It is. For example, "It's hot today." It's = It has. For example, "It's b...

Creat a time book
I'm building a semi automated time book in Access. what i want is to be able to give access a two week period prefferably by specifying the beginning and end dates and have access add an entry to a table i'm going to call the 'Time Book' for each person in a personnell table for each day. the best i have been able to come up with is to pack a Macro with 14 queries, each adds one more day to a specified starting point. one of the problems i'm running into is that some of the shifts run over night and Access doesn't calculate the shift end correctly. I wo...

Front end server with mail boxes??? can it be done #2
sorry for the late post... i have 50 users "joel" <joelbueno@verizon.net> wrote in news:ehG8e.8669$c93.2155@trnddc08: > sorry for the late post... i have 50 users Why? Are you going to have back-end servers? Regards, -- Arlo Clizer Exchange MVP FAQ: http://www.exchangefaq.org Archives: http://groups.google.com "joel" <joelbueno@verizon.net> wrote: >sorry for the late post... i have 50 users No. -- Rich Matheisen MCSE+I, Exchange MVP MS Exchange FAQ at http://www.swinc.com/resource/exch_faq.htm ...

Category totals over time...
Good morning... Is there a way within Money 2007 to graph the income or expense in a given category over time? For example, I might want to graph the monthly sums of my Dining Out expense over the past few years. -Ed In microsoft.public.money, Ed Markovich wrote: > >Is there a way within Money 2007 to graph the income or expense in a >given category over time? > >For example, I might want to graph the monthly sums of my Dining Out >expense over the past few years. You could start with the Income and Spending Over Time report. Customize to select just the category you w...

Outlook 2003 "Full Name..." field in contact screen reversed.
Outlook 2003 "Full Name..." field in contact screen reversed. I am using XP, with Outlook 2003. For some reason, all my 800 contacts are having a problem. When I open the contact screen the "Full Name... " field is displaying names backwards. "Mr. John Smith" displays as "Mr. Smith John" I have checked my "Contacts Options" selection for default Full Name order and it correctly displays "First (Middle) Last"... I have noticed that if I delete the name and re-enter it, it displays correctly, however I cannot spend the time to d...

Attachments not received #2
Can anyone help ? - When I send some attachments using Outlook 2003 the attachments are not received by the recipient just the mail. Incidentally they show the attachment paperclip in the Sent Items as if they were sent. This happened in earlier version too but only when I switched to XP. It does not occur with Outlook Express Many thanks First thing to check is your sending mail format. Make sure you're not using Rich Text Format. Try Plain Text or HTML instead "Marian Hughes" <moi@esatclear.ie> wrote in message news:OMWri8Y4DHA.1592@TK2MSFTNGP10.phx.gbl... >...

how can i start using excel for the first time?
i cant figure out how to get excel to work for me and im a first time user of it? i Cant get nothing to work on it? can anyone please help me here Paul Can you get Excel to start up? Can you get a blank workbook to open via File>New? For basics on Excel see.......... http://www.usd.edu/trio/tut/excel/index.html http://www.baycongroup.com/el0.htm Microsoft Training Courses. http://office.microsoft.com/en-us/training/CR061831141033.aspx Gord Dibben Excel MVP On Wed, 15 Dec 2004 15:39:02 -0800, "Paul Scheffer" <Paul Scheffer@discussions.microsoft.com> wrote: >i ...