DateDiff problem

V

varunmathur

Registered User.

Local time

Today, 03:43

Joined

Feb 3, 2001

Messages

68

• Nov 7, 2012

• #1

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

Smeghead

Local time

, 20:43

Joined

Jan 12, 2001

Messages

32,059

• Nov 7, 2012

• #2

Does your starttime and endtime have BOTH date AND time?

 

V

varunmathur

Registered User.

Local time

Today, 03:43

Joined

Feb 3, 2001

Messages

68

• Nov 7, 2012

• #3

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

Smeghead

Local time

, 20:43

Joined

Jan 12, 2001

Messages

32,059

• Nov 7, 2012

• #4

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

 

V

varunmathur

Registered User.

Local time

Today, 03:43

Joined

Feb 3, 2001

Messages

68

• Nov 7, 2012

• #5

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

Super Moderator

Local time

Today, 03:43

Joined

Nov 30, 2011

Messages

8,494

• Nov 7, 2012

• #6

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

 

boblarson

Smeghead

Local time

, 20:43

Joined

Jan 12, 2001

Messages

32,059

• Nov 7, 2012

• #7

varunmathur said:

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

Click to expand...

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\#))

 

V

varunmathur

Registered User.

Local time

Today, 03:43

Joined

Feb 3, 2001

Messages

68

• Nov 7, 2012

• #8

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

Super Moderator

Local time

Today, 03:43

Joined

Nov 30, 2011

Messages

8,494

• Nov 7, 2012

• #9

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..

 

Last edited: Nov 7, 2012

V

varunmathur

Registered User.

Local time

Today, 03:43

Joined

Feb 3, 2001

Messages

68

• Nov 7, 2012

• #10

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

 

V

varunmathur

Registered User.

Local time

Today, 03:43

Joined

Feb 3, 2001

Messages

68

• Nov 7, 2012

• #11

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