DATE CALCULATION PROBLEMS

[Greg]

Hi all,

I have a Userform into which I insert dates to calculate the number of days between the two dates.

Until recently I thought this was working successfully. Indeed for some dates it does. For example if I enter a first date (12/12/2005) and a second date (12/12/2006) the correct number of days (365) is returned and used in an interest calculation on my document.

However, if I enter 07/04/2006 (i.e. 7 April 2004) and 30/10/2007 (i.e. 30 October 2007) I get 571 days. I have no idea why one set of dates will produce the correct answer and the other won't.

Does anyone have any suggestions?

Greg.

[OTWarrior]

my guess it that it should be 483 days, and i am fairly certain it is working out the dates as american format (mm,dd,yyyy) rather than uk (dd,mm,yyyy), which is how access calculates dates

[fumei]

Is not the correct answer 571 days? Just to break it down....

mm-dd-yyyy format

Date 1: 04/07/2006 (April 7, 2006)
Date 2: 10/30/2006 (Oct. 30, 2006)

Difference? 206 days

206 + 365 (to make it the next year 2007) = 571

Date 1: 07/04/2006 (July 4, 2006)
Date 2: 10/30/2007 (Oct. 30, 2007)

Difference? 483 days

It seems to me that you are possibly flipping formats between mm-dd-yyyy and dd-mm-yyyy. One of your inputs is one way, the second the other way.

What exactly is your code?

[Greg]

You are right. I don't have a problem at all. Please mark as solved.

[Greg]

Actually, the problem isn't quite solved.

In Excel I get a daily interest rate calculated to 3 decimal points. This produces a different result to the Word Datediff calculation which is calculated to only 2 decimal points.

Is there a way in which I can get a more accurate calculation from the Datediff function?

Regards,

Greg.

[fumei]

Ummm, DateDiff does not calculate interest rates. Nor does it return decimal numbers.

DateDiff returns one thing only - the interval between the dates, expressed as one of:

yyyy Year
q Quarter
m Month
y Day of year
d Day
w Weekday
ww Week
h Hour
n Minute
s Second

It returns an integer, no decimals at all.

Again, what exactly is your code?