+ Reply to Thread
Results 1 to 7 of 7

IF formula between dates doesn't work, returns TRUE instead of FALSE

  1. #1
    Registered User
    Join Date
    03-16-2016
    Location
    Berlin
    MS-Off Ver
    MS Office 2013
    Posts
    28

    Question IF formula between dates doesn't work, returns TRUE instead of FALSE

    Hello excel friends,

    I hope you had a nice week so far.
    I am stuck with a formula and I just don't get why it's not working. I've been trying a lot, but I am not able to figure it out. Therefore I hope you guys could hep me out, I believe it's pretty easy for you.

    So it's actually just part of the formula that doesn't work, that is why I simplified it a lot.
    I have a contract with a start date and it's financial impact is always 2 months after the actual start.
    For that reason I tried to use a MONTHS & YEARS formula in order to solve it. The conditions are the following.

    IF the actual month is before the start that then leave it empty (or in this case return TRUE)
    IF the actual month is 2 or more months after the start that then return FALSE

    I create the formula =IF(MONTH($B5)+2&YEAR($B5)>MONTH(L$3)&YEAR(L$3),"TRUE","FALSE")

    For some reason the formula works, but for some reason starting from October it doesn't work anymore, and I simply do not understand why. If I take a look into the formula it would be IF(62019>102019), which should be FALSE, but for some reason it returns TRUE. Why is that?

    I attached you a excel with 1 line that includes the formula and another line that shows how it supposed to be. Please have a look and let me know if that's enough of information.

    I hope you guys can help me out! Thanks sooo much in advance!

    Best,
    AFGP
    Attached Files Attached Files
    Last edited by AFGP; 08-21-2019 at 08:09 AM. Reason: better title

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,780

    Re: IF, MONTH, YEAR - formula problem.

    Administrative Note:

    We would very much like to help you with your query, however the thread title does not really convey what your request is about.

    Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

    Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

    (Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    03-16-2016
    Location
    Berlin
    MS-Off Ver
    MS Office 2013
    Posts
    28

    Re: IF, MONTH, YEAR - formula problem.

    Hello AliGW,

    Thanks for the feedback. I tried to describe it slightly better, the problem is that it seems like a very generic problem in the first place.

    Best,
    AFGP

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,780

    Re: IF formula between dates doesn't work, returns TRUE instead of FALSE

    It won't be a generic problem - it will be either user error or incorrect user expectation.

    Thanks for the title change - I'll have a look at your file.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,780

    Re: IF formula between dates doesn't work, returns TRUE instead of FALSE

    OK. Question for you: which is larger?

    62019

    102019

    You'd expect 102019, wouldn't you?

    However, you are actually comparing text, so Excel is confused.

    This will deal with the dates in 2019:

    =VALUE(MONTH($B5)+2&YEAR($B5))>VALUE(MONTH(G$3)&YEAR(G$3))

    However, the formula is not robust, and falls over with the 2020 dates.

    Try this instead, which will work in all cases:

    =EOMONTH(EDATE($B5,2),-1)+1>G$3

  6. #6
    Registered User
    Join Date
    03-16-2016
    Location
    Berlin
    MS-Off Ver
    MS Office 2013
    Posts
    28

    Re: IF formula between dates doesn't work, returns TRUE instead of FALSE

    Okay, so that is why it didn't work.

    =EOMONTH(EDATE($B5,2),-1)+1>G$3
    This formula works just perfectly. I would have never come up with that, so thank you very much, really!


  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,780

    Re: IF formula between dates doesn't work, returns TRUE instead of FALSE

    You're welcome!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Problem with VBA code to transfer only current month and year.
    By ElmerFud in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-28-2015, 12:03 PM
  2. Running Totals from 24 hours to month, month to year without year decreasing
    By Safetyintern in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-20-2015, 01:13 PM
  3. If Column A Month and Year = ColumnB Todays Month and Year then send email
    By HACCStaff in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-15-2013, 12:47 AM
  4. Problem w Code to switch month and year
    By ChemistB in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-09-2011, 04:19 PM
  5. Replies: 4
    Last Post: 02-14-2010, 05:21 AM
  6. formula for prior month & year of a month end date.
    By mikeburg in forum Excel General
    Replies: 2
    Last Post: 09-25-2007, 04:01 PM
  7. problem with entering month of the year
    By S S in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-21-2006, 07:25 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1