+ Reply to Thread
Results 1 to 6 of 6

Comparing dates as IF condition (empty cell not recognized)

  1. #1
    Registered User
    Join Date
    02-12-2015
    Location
    Estonia
    MS-Off Ver
    2010
    Posts
    3

    Comparing dates as IF condition (empty cell not recognized)

    Hi,

    I'm trying to write a formula that would check the value in cell M2 and compare it to current year and week. The cell value is in format "2015-15" (year and week number). If M2 is in the future, it should say that work is in progress, if not, a long long formula will follow (which is working, I tried it separately). What I've tried so far is this:

    =IF(AND(VALUE(MID(M2;6;2))>=(WEEKNUM(TODAY();2));VALUE(LEFT(M2;4))=(YEAR(TODAY())));"In progress";../rest of the formula/..)

    The problem is that the cell M2 could also be empty and then it should go on with "the rest of the formula" because the IF condition is not true, but it doesn't recognize an empty cell. I'm guessing it has something to do with the MID() part of it, because I'm telling the cell should have at least 6 characters in it. But I figured if the condition is not true, it would still go on with the formula, but it won't.

    Any ideas why this isn't working or how I should compare the dates?
    Thanks in advance :)

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,064

    Re: Comparing dates as IF condition (empty cell not recognized)

    UPDATE: Oops! Formula updated, try now.

    =IF(M2="","",IF(M2>YEAR(TODAY())&"-"&(RIGHT("0"&WEEKNUM(TODAY()),2)),"Work in progress","Not in future"))

    or possibly

    =IF(AND(M2<>"",M2>YEAR(TODAY())&"-"&(RIGHT("0"&WEEKNUM(TODAY()),2))),"Work in progress","rest of formula")
    Last edited by Special-K; 02-12-2015 at 08:50 AM.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Registered User
    Join Date
    02-12-2015
    Location
    Estonia
    MS-Off Ver
    2010
    Posts
    3

    Re: Comparing dates as IF condition (empty cell not recognized)

    Wow, that was fast.

    I tried
    =IF(AND(M2<>"",M2>YEAR(TODAY())&"-"&(RIGHT("0"&WEEKNUM(TODAY()),2))),"Work in progress","rest of formula")

    It seems to work as long as the cell M2 is empty, year is in the past (eg 2014-35) or year and week are in the future (eg 2015-35). It doesn't work though when it's this year but in the past (eg 2015-2), then it tells me that work is in progress, though it shouldn't.

  4. #4
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,064

    Re: Comparing dates as IF condition (empty cell not recognized)

    The week number must be 2 digits, ie 2015-02 not 2015-2

    2014-51 produces rest of formula
    2015-01 produces rest of formula
    2015-08 and above produces work in progress
    Blank produces rest of formula

  5. #5
    Registered User
    Join Date
    02-12-2015
    Location
    Estonia
    MS-Off Ver
    2010
    Posts
    3

    Re: Comparing dates as IF condition (empty cell not recognized)

    Thanks so much

  6. #6
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,064

    Re: Comparing dates as IF condition (empty cell not recognized)

    Try this, doesnt matter if week number is 1 or 2 digits

    =IF(AND(M2<>"",IF(LEN(M2)=7,M2,LEFT(M2,5)&"0"&RIGHT(M2,1))>YEAR(TODAY())&"-"&(RIGHT("0"&WEEKNUM(TODAY()),2))),"Work in progress","rest of formula")

+ 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. Replies: 1
    Last Post: 05-26-2014, 01:31 PM
  2. [SOLVED] Comparing dates with extra condition
    By Wayprof in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-09-2013, 12:25 PM
  3. [SOLVED] counting non-empty cell while comparing dates
    By calitopgun in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-17-2013, 02:23 PM
  4. [SOLVED] Column of dates not recognized as dates...
    By nanomess in forum Excel General
    Replies: 8
    Last Post: 04-12-2013, 02:42 PM
  5. macro to colour empty cells (cells not recognized as empty)
    By Gerben in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-30-2005, 11:05 AM

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