DATEDIFF()?? (XP)

Topic

New Reply

Is there logic that I am not seeing in the following results using DateDiff(“m”,[tmpTest]![startDate],[tmpTest]![endDate]) :
startDate endDate intMonths
1/1/2000 1/30/2000 0
1/1/2000 2/1/2000 1
1/1/2000 2/29/2000 1
1/1/2000 12/31/2000 11
1/31/2000 12/31/2000 11

Shouldn’t Jan 1-Dec 31 be 12 months?
Shouldn’t Jan 1 – Feb 29 be 2 months?
Shouldn’t Jan31 – Feb 1 be 0 months?

Perhaps I am using the wrong function or the right function in the wrong way. What I am attempting to accomplish is calculate the number of months between two dates, and based on the above I am getting information of little use (aka “useless information”). There is a function in Excel, “YEARFRAC()”, that does exactly what I am seeking, and I thought the DateDiff was essentially the same.

Any ideas and thoughts are greatly appreciated.

Ken

Reply | Quote

Replies

Oops! I found the old “standby” worked for this as well. “((([EndDate]-[startDate])/365.25)*12)” where resulting field has zero decimal places

I would like to know more about the DateDiff function, if anyone has time and inclination.

It defies logic in my small brain. What WOULD be an effective use of the DateDiff?

Thanks again.

Ken

Reply | Quote

DateDiff is an extremely simple (stupid, if you like) function. DateDiff(“m”, Date1, Date2) subtracts the month of Date1 from the month of Date2, regardless of the day of the month of both dates. If you need that, fine. Otherwise, you’ll have to use more complicated functions (perhaps utilizing the result of DateDiff)

Reply | Quote

Thanks Hans. It is me that is dumb, and not the function. Thanks for the help!

Reply | Quote

Thanks Hans. It is me that is dumb, and not the function. Thanks for the help!

Reply | Quote

DateDiff is an extremely simple (stupid, if you like) function. DateDiff(“m”, Date1, Date2) subtracts the month of Date1 from the month of Date2, regardless of the day of the month of both dates. If you need that, fine. Otherwise, you’ll have to use more complicated functions (perhaps utilizing the result of DateDiff)

Reply | Quote

Oops! I found the old “standby” worked for this as well. “((([EndDate]-[startDate])/365.25)*12)” where resulting field has zero decimal places

I would like to know more about the DateDiff function, if anyone has time and inclination.

It defies logic in my small brain. What WOULD be an effective use of the DateDiff?

Thanks again.

Ken

Reply | Quote