Comparing Months

Posted on 2011-10-30
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
    LVL 92

    Expert Comment

    by:Patrick Matthews
    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

    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 47

    Accepted Solution

    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

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

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    745 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now