Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Comparing Months

Posted on 2011-10-30
Medium Priority
Last Modified: 2012-05-12
I need to simply compare if the difference in months from two dates are greater than zero or less than zero.  Using DateDiff("m"... ) is fine except for December versus January.  I'm not concerned with the exact days in between but just the true difference in months and years.  Is there a formula?
Question by:error_prone
  • 2
LVL 93

Expert Comment

by:Patrick Matthews
ID: 37054170
Define "true difference in months and years".

And if all you care about is whether two dates are in the same calendar month, DateDiff will ALWAYS work.

Author Comment

ID: 37054185
But I'm not just concerned with if they're in the same calendar month but if the difference is greater than zero or less than zero.
LVL 49

Accepted Solution

Dale Fye earned 2000 total points
ID: 37054229
Well, DateDiff("m", FirstDate, SecondDate) will return either a negative number, 0 or a positive number.

You can use the sign function sgn(DateDiff("m", FirstDate, SecondDate)) to get either a -1, 0 , or 1.

You could use the Choose function to return some other value that is related to the -1, 0, or 1, something like:

Choose(sgn(Datediff("m", FirstDate, SecondDate)) + 2, "less than", "same", "Greater than")

Author Comment

ID: 37054241
Sorry, false alarm - I thought DateDiff("m",#12/31/2011#,#1/1/2012#) would give me the wrong results.

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Implementing simple internal controls in the Microsoft Access application.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question