How to find whole months only (Access 2k Win 2k)

Topic

New Reply

Hello again

I’m trying to work out how to count the total number of whole months between any two dates. Is there an easy way of doing this, or will it need some serious SQL / VBA?

Thanks

ian

Reply | Quote

Replies

The DateDiff() function can be set to calculate the difference in months between two dates, but I’m not sure what you mean by whole months between two dates. For example if date 1 is March 1 and date 2 is June 30 is the answer 4? And if date 1 is March 2 and date two is June 29 is the answer 2? I haven’t tested DateDiff, but I think it may give the same answer in both cases.

Reply | Quote

The DateDiff() function can be set to calculate the difference in months between two dates, but I’m not sure what you mean by whole months between two dates. For example if date 1 is March 1 and date 2 is June 30 is the answer 4? And if date 1 is March 2 and date two is June 29 is the answer 2? I haven’t tested DateDiff, but I think it may give the same answer in both cases.

Reply | Quote

If you have two dates Date1 and Date2, the number of whole months between them is

DateDiff(“m”, Date1, Date2) + (Day(Date1) > Day(Date2))

DateDiff just takes the months of Date1 and Date2 into account, it doesn’t look at the day in the month of either date. (Day(Date1) > Day(Date2)) is either True (-1) if the first date is on a later day in the month than the second date, or False (0). We use this to subtract 1 from the number of months if the first date is on a later day in the month than the second date.

If you need this often, you could create a custom VBA function for this.

Reply | Quote

If you have two dates Date1 and Date2, the number of whole months between them is

DateDiff(“m”, Date1, Date2) + (Day(Date1) > Day(Date2))

DateDiff just takes the months of Date1 and Date2 into account, it doesn’t look at the day in the month of either date. (Day(Date1) > Day(Date2)) is either True (-1) if the first date is on a later day in the month than the second date, or False (0). We use this to subtract 1 from the number of months if the first date is on a later day in the month than the second date.

If you need this often, you could create a custom VBA function for this.

Reply | Quote