Date/Time field calculation

Hello,
i am trying to make a sum calculation on a field that keeps duration of 
time. How can i do this? I get errors or zero as result.
Thanks
0
Stathis
6/21/2007 4:02:06 PM
access 16762 articles. 3 followers. Follow

1 Replies
1101 Views

Similar Articles

[PageSpeed] 35

On Jun 21, 10:02 am, Stathis <s_paraske...@hotmail.com> wrote:
> Hello,
> i am trying to make a sum calculation on a field that keeps duration of
> time. How can i do this? I get errors or zero as result.
> Thanks

You need to do the calculation at a level at least 1 level below what
you want the answer to be in.

Example
    msgbox DateDiff("h", #6/20/2007 1:00:00 AM#, #6/20/2007 1:30:00
PM#)
              will give an answer of 12       (not 12.5)

    msgbox DateDiff("n", #6/20/2007 1:00:00 AM#, #6/20/2007 1:30:00
PM#)
             will give an answer of 750     with you can now  divide
by 60 to give you 12.5 (probably in a different field with it's own
definition to include the decimal.

Ron


0
Ron2006
6/21/2007 3:23:49 PM
Reply:

Similar Artilces:

Date format issue when submitting from a userform to a spreadsheet
Hi, I have a userform that I've generated which routes dates onto a spreadsheet based on the users input. I am having a bit of a frustrating time with the dates, it would appear that in the process of moving the date from the userform to the spreadsheet some dates are switched/transposed. I'll give an example. If someone enters 09/02/2004 on the userform excel seems to look at 09 and assume it is a month and transposes the dates to 02/09/2004(this does not appear to be a US/UK format issues as I have already gone down that road). However, if the date 13/09/2004 is entered i...

Using iterations to calculate circular references.
I am working with data that requires using circular references in some calculations. I checked the "iterations" box to allow for these calculations to work, and Ecxel seems to be calculating everything correctly. The problem is that after I close the workbook, when I later reopen it to continue working, all the cells that are a part of the circular reference display the #VALUE! error. I can fix this by deleting one cell that is part of the loop and then undoing that command, or by copying the formula from a cell, deleting it, and pasting it back into the same cell. I have to d...

varible calculation
I am trying to create a calculation for haulage/trucking charges 1-300 kilos £ 30 300-400 kilos £ 35 400-500 kilos £ 40 What formula could I use to accomplish this?? -- kinsey assuming that you mean: 1-299 300-399 400- then try: =LOOKUP(A1,{0,300,400},{30,35,40}) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk "kinsey" <kinsey@discussions.microsoft.com> wrote in message news:46C0E5C2-11A6-4AF7-9F2A-8D4D23F4498B@microsoft.com... &...

calculating return in a range
Hi, I have a range of stock prices. with indication for buy or sell. and i want to calculate the return of the range for example : buy 522.73 buy 527.9 buy 528.81 buy 532.33 buy 535.1 buy 536.23 buy 537.03 buy 538.57 sell 537.54 sell 532.86 sell 538.14 sell 533.08 buy 531.22 I want to calculate the return for the first buy signal. the range is from the start to the first sell signal 537.54- 522.73 then the sell range untill the first buy signal 531.22- 537.54 and so on.... -- kman ------------------------------------------------------------------------ kman's Profile: http://www.e...

SUMIF function not calculating from closed worksheet
We recently upgraded from Microsoft Office 2000 to 2007. Prior to the upgrade each department would access a spreadsheet that would load data from a common lookup spreadsheet The loading process was performed as follows: The lookup file and each department file would be opened to update the link for that month and then saved and closed. When the department head would open the department file, a question would appear asking if they wanted to update the link, if they answered no the spreadsheet would open and display the calculated data. If they answered yes, the file would o...

Suggestion field
Hello, does anyone know how they call the suggestion field that appears at the explorer bar when you type an address. Or appears when you double-click on a username field. I would like to create something like that; maybe someone knows how to do that. It's probably simple but because I don't know the exact English word for it, it's hard to find. Thanks. You might want to take a look at IAutoComplete. Here is an interesting article that talks about it a bit. Tom http://msdn.microsoft.com/msdnmag/issues/0800/c/default.aspx "Woestenburg" <Woestenburg@discussions.micr...

Question On Fields
What is the (Microsoft desciption)difference between the SIC field and the Industry Code field? To me they seem like a duplication. Thanks! Shauna Hi, According to the Deployment Manager descriptions of attributes: SIC - Standard Industrial Classification code for the Account industrycode - the type of industry with which the account is associated... Hope this helps! "Shauna Koppang" <anonymous@discussions.microsoft.com> wrote in message news:034101c48af4$62524a70$a401280a@phx.gbl... > What is the (Microsoft desciption)difference between the > SIC field and the...

How to create a CustomerID field for custom entities
Hi, The customerid field is used in opportunity and case and it allows you to select an account or contact, Great. I want to create this field in a form for a new entity i have made, but i cant work out how. Please help. Hi Michael, Customer is special composite type which is not available for custom entities in CRM 3. You will need to add two separete lookup fields for account and contact. -- uMar Khan :: MS CRM MVP CRM Freelance Consultant Email :: imumar at gmail dot com Blog :: http://umarkhan.wordpress.com MVP :: https://mvp.support.microsoft.com/default.aspx/profile/umar.khan &...

Sorting Alphanumeric values in a text field
I'm using Access 2003 for a database for my company. I have a field in a table that has both text and numbers. They are part numbers, for example 21BC124. I kept the field as text because of the text with in the numbers and didn't figure that a numeric field would alow the text. In my part numbers table it sorts correctly (first by number then by letter then by number again), but in my reports and queries there are a few number that sort in the wrong place. Like this... 20D10-3 21BC123 21BC128 22D10 25TD47 21FA101 21FA200 25FA203 38FA601 21FP604 38WS100 I can't quite f...

Subform in Datasheet View
Is there a way to have the datasheet view of a subform *not* show the "button-like" row that shows the field names? -- Thanks, tbl No there isn't. If you create a continuous style form, you can eliminate the headings. You can modify a continuous form so that it looks like a 'datasheet'. -- Joan Wild Microsoft Access MVP "tbl" <hateThatSpam@invalid.net> wrote in message news:4gqs13dds31nhu1bsmbrm5tt86adbfea1m@4ax.com... > Is there a way to have the datasheet view of a subform *not* > show the "button-like" row that shows the field n...

Run-time error '91:
Hi I get a Run-time error '91' object variable or with block variable not set error once in a while every other day or so I have Rms 2.0.0126 sp3. any help I just had the same error happening a couple weeks ago. I was able to narrow it down to one tender that was producing the errors. I renamed that tender with "(old)" at the end of it and created a new one. The error hasn't happened again. "John" wrote: > Hi > I get a Run-time error '91' object variable or with block variable not set > error once in a while every other day or so I have ...

Keyboard shortcut for current date and time
Hi, Ctrl+ ; inserts current date and Ctrl+Shift+; inserts current ti me Ctrl+Shift+; inserts the current time with the date serial as 0 and not the current date's date serial. Presently I am adding the two (ie current date and time) to get the current date and time. Is there a keyboard shortcut that does this? Thanks in advance. Regards, Raj CTRL+; then SPACE then CTRL+SHIFT+; -or- =Now() -- Jim Cone Portland, Oregon USA http://www.mediafire.com/PrimitiveSoftware "Raj" <rspai9@gmail.com> wrote in message Hi, Ctrl+ ; inserts current dat...

Set default time zone for all users
Is there a way to make the time zone default to a certain value so that I don't have to go into every user and change it to the correct zone? No. Each user must change their own timezone settings. I have no idea why Microsoft do not pick up your computers timezone. Hopefully this will be changed in a version 2.0 release "Jack Black" <anonymous@discussions.microsoft.com> wrote in message news:6F5F4A10-B6AD-420C-9ACB-F73A13FDABB9@microsoft.com... > Is there a way to make the time zone default to a certain value so that I don't have to go into every user and change i...

Appointment times won't post correctly
On the calendar, appointments, meetings, etc. made or received show up on the date and time received or sent; regardless of the actual date or time of the appointment. Is there a setting that will correct this? I've exhausted myself trying to find a fix. Outlook 2003 There is a seperate calendar time zone setting that has to be set in addition to your system clock. Pain in the ass. Go to Options and then select Calendar. You should see the Time Zone button there. Set it accordingly, but be aware that everything in your calendar up to that point will change. "Keith Wilso...

office 2003 w/ windows 7 requires reacceptance ot T&C's each time
office 2003 w/ windows 7 requires reacceptance of T&C's each time i open work or excell or any office program. How do I get rid of this? Boot into Safe mode in Windows 7,open any Office app and then accept the terms -- Peter Please Reply to Newsgroup for the benefit of others Requests for assistance by email can not and will not be acknowledged. "mouser830" <mouser830@discussions.microsoft.com> wrote in message news:E7B7602D-F5D8-4559-98EF-A2CCC1E2D6FD@microsoft.com... > office 2003 w/ windows 7 requires reacceptance of T&C's each time i op...

Compare Now() to a European date
This is driving me nuts, I have a list of certificates. In column B their expiry dates are entered as Europeans, some at least, do. Like today would be 20080524. I want these cells to change colour with conditional formating. For instance becoming yellow when there is less than three months between now and the expiry date, and then becoming red when there is less than one month to expiry. Else they should remain without colour. I have read through a hundred posts dealing with similar needs and seemingly fine replies, but I get nowhere with my particular sheet. When I format my B cell as ...

Formula for date field
1.I have simple dates in one column (say column A) . 2.In the next column(Column B) I would like the date five months after Column A to be displayed.Eg if Column A has an entry of 9th June 2007,Column B should display 8th November,2007. 3.A simple formula does not do the job as this does not take into account the different number of days in different months! regards S.Sanatani Your post is a bit ambiguous since you don't really say how the different number of days in months should be handled. One way: A1: <date> B1: =DATE(YEAR(A1),MONTH(A1)+5,DAY(A1)-1) In articl...

Date Format turn to Year
Hi, I tried to convert the date to YEAR and then the year plus 25 Years later. =Year(A1) I'm getting the result 1900 instead of 1965. I tried to add 25 years later to 1990 from 1965. Your help would be much apprecated. Thanks What's in A1? Are you sure it's a real date? "learning_codes@hotmail.com" wrote: > > Hi, > > I tried to convert the date to YEAR and then the year plus 25 Years > later. > > =Year(A1) I'm getting the result 1900 instead of 1965. > > I tried to add 25 years later to 1990 from 1965. > > Your ...

OAB field size limitations
Hi, we've noticed several user display names getting cut off within the offline address book. There appears to be a limit of 64 characters. If I look at the corresponding user entry in the "live" address book it displays correctly. If I search for the user in my copy of the offline address book it is cut off at the 64 character mark. Does anyone know if this is indeed a limitation of the OAB? I can't seem to find much about this anywhere. OAB property filtering for Exchange 2003 Service Pack 2 OAB Version 4 - http://blogs.msdn.com/dgoldman/archive/2006/04/29/586827.asp...

Changing the Selection Fields in a Combo Box
Hi, I've created a Combo Box using the Controls Wizard. In creating the control, I specified that my unique value to be stored in the table is an ID. When the user views the selection options in the Combo Box, I'd like to have the ID field hidden and only see the text values, while still storing the ID in the appropriate column. This is probably a second question. Also, when the user views a form that has been populated, I'd like them to see the actual textual values on the form, as just the stored ID value. Is that possible to do with the one Combo Box or is that...

Contact looses Post Office Box field when synchronized to Outlook
When I sync contacts from CRM 4.0 to Outlook, the Post Office Box field from the contact gets empty. Anyone has the same issue? ...

using dates Part 2
Karl was great in helping me get to this point with dates, now I'm wondering if we can take it 1 step further? For Activity Dates prior to 2/1/2007 they are using a normal reporting year and the formulas below take care of Activity dates >2/1/2007? So for example prior to 2/1/2007 1/1/2006 would have a B_Qtr of 2006-1 1) B_Qtr - 2011-1 --- Format(DateAdd("m",11,[ActivityDate]), "yyyy - q") 2) Year - 2011 ---- Year(DateAdd("m",11,[ActivityDate])) 3) Qtr - Q1 ---- Format(DateAdd("m",11,[ActivityDate]), "q") -- Than...

Today's date on an Active X Calendar
Could anyone tell me how to set the properties so that the ActiveX calendar I have in the database, displays the current date when the program is openend. I thought this would have been easy, but obviously not! Thanks for any help. CEL504 wrote: > Could anyone tell me how to set the properties so that the ActiveX calendar I > have in the database, displays the current date when the program is openend. > > I thought this would have been easy, but obviously not! > > Thanks for any help. http://groups.google.com/group/comp.databases.ms-access/msg/1564d683ede98f8c Jame...

How can I parse or Loop a list based on single field to Googlemaps
This is some code that will parse each sequential field [Point_#) into a list for googlemaps. [Form_Runs].WebRouteMap.navigate "http://maps.google.co.uk/maps?f=q&hl=en&q=" & "from: " & Me.Point_1 & (" to: " + Me.Point_2) & (" to: " + Me.Point_3) The table/form is laid outlike this: [Route_No] [Point_1] [Point_2] [Point_2] 5 North St, N1 East St, N3 High Road, W6 And the code will give this style of parsed list: from: North St, N1 to: East St, N3 to: High Road, W6 That works just ...

time #11
I have set up a spreadsheet with time based on what our company truck drivers work. We work under a DOT rule of 70 hours / 8 days. That means that the driver can not work more than 70 hours in an 8 day period. The sheets I have (one for each month) goes back 7 days plus the current day and calculated how many hours a day the driver works and subtracts it from 70, leaving how many hours they can work the next day. If the calculation gets to 0 or above, the cell changes color to alert me that the driver's 70 hours are up and he/she has to have 34 hours off before he/she can be on duty ...