DateDiff problem

[varunmathur]

Hi
I am using the code
minutes: (DateDiff("n",[starttime],[endtime]) returns a number 59356850 instead of the correct answer which would be 11 min
both starttime and endtime are General dates
need help, what am i doing wrong
:banghead:

 

[boblarson]

Does your starttime and endtime have BOTH date AND time?

 

[varunmathur]

Yes both are of the format General Date, display both date and time, I earlier tried long date format but the problem remained the same.

 

[boblarson]

What are the valued in the fields as they are now which are yielding those results?

 

[varunmathur]

Yes, both fields are of the general date format, and display both date and time, I tried to work with long time format but the problem persisted

 

[pr2-eugin]

What Bob meant was show us a sample data, that is returning that crazy result..

 

[boblarson]

Yes, both fields are of the general date format, and display both date and time, I tried to work with long time format but the problem persisted

I will ask again. What are the actual values in the fields that are getting that result you posted - you know, the ones that SHOULD yield 11 minutes but are yielding a considerable higher amount? I want to test something but need the actual values you have in those fields that result in that answer.

EDIT: And when typing this out, it may have dawned on me what has occurred. Give this a try:

minutes: DateDiff("n",Format([starttime], "\#mm\/dd\/yyyy\#),Format([endtime], "\#mm\/dd\/yyyy\#))

 

[varunmathur]

The dates in the fields are:-
StartDate EndDate
07-11-2012 06:55:05 07-11-2012 07:02:35
07-11-2012 12:50:03 07-11-2012 12:58:55

 

[pr2-eugin]

Varun try Bob's suggestion... The problem we think what is happening here would be the Formatting of date.. as 07-11-2012 can be 07 Nov 2012 if it is UK format OR 11 July 2012 if it is US Format.. Changing it to a default format as mentioned in Post #7 might help..

 

[varunmathur]

Thanks sir,
I think the format may be the error, I shall try amending the format

 

[varunmathur]

Thanks a lot
I reformatted the table to set the date format :- dd/mmmm/yyyy hh:nn
the datediff started working correctly
Thanks again Mr Bob