+ Reply to Thread
Results 1 to 6 of 6

If cell meets date requirement enter 1, if not enter 0, if blank don't enter anything.

  1. #1
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Asheville NC
    MS-Off Ver
    Excel for Office 365 MSO
    Posts
    111

    Question If cell meets date requirement enter 1, if not enter 0, if blank don't enter anything.

    I am trying to use a IF statement to return one of 3 results but can't figure it out. Thanks for any help.

    If the cell date is prior to the set date, enter the number 1.
    If the cell date is after the set date, enter the number 0.
    If the cell is blank, do nothing.

    Example:
    Set date = 11/1/2017, located in cell A4.
    Cell to be evaluated is located in B5.
    Formula entered into cell C5.
    If B5 is blank, how do I get C5 to also be blank? I've tried:
    1) IF(B5<A4,1,0), IF(B5="","",??)

  2. #2
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: If cell meets date requirement enter 1, if not enter 0, if blank don't enter anything.

    =IF(c4="","",if(CONDITION,IF TRUE,IF FALSE)

    Thanks
    If myself or others have helped, please add to our reputation by pressing the 'Star' icon below this.

  3. #3
    Registered User
    Join Date
    01-19-2017
    Location
    Michigan
    MS-Off Ver
    2013
    Posts
    92

    Re: If cell meets date requirement enter 1, if not enter 0, if blank don't enter anything.

    I believe this will work. Let me know!

    =IF($A$4<B5,1, IF($A$4>B5,0,""))

    Works except for the darn thing won't do anything to the blank cell, I'm sure someone who is more skilled can spot my error.
    Last edited by Xandler; 05-11-2017 at 12:42 PM. Reason: formula change

  4. #4
    Registered User
    Join Date
    01-19-2017
    Location
    Michigan
    MS-Off Ver
    2013
    Posts
    92

    Re: If cell meets date requirement enter 1, if not enter 0, if blank don't enter anything.

    Seems Luke was correct!

    =IF(B5="","",IF($A$4>B5,1,0))

    That should solve your problem!

  5. #5
    Forum Contributor
    Join Date
    05-08-2015
    Location
    Asheville NC
    MS-Off Ver
    Excel for Office 365 MSO
    Posts
    111

    Re: If cell meets date requirement enter 1, if not enter 0, if blank don't enter anything.

    Thanks for the reply. I tired yours and if the cell is blank, the result returned is 1. If the cell was blank, I wanted nothing returned (""). I tried yours also with an OR statement in place fo the AND statement and still got a result of 1. The formula Luke Gilfoyle suggested worked. Thank you though for your suggestion.

  6. #6
    Registered User
    Join Date
    01-19-2017
    Location
    Michigan
    MS-Off Ver
    2013
    Posts
    92

    Re: If cell meets date requirement enter 1, if not enter 0, if blank don't enter anything.

    Yep! I tried them with and as well as or, I was writing it using the wrong references, but glad it works now!

+ 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: 03-25-2016, 01:31 AM
  2. Replies: 1
    Last Post: 03-25-2016, 01:16 AM
  3. [SOLVED] Enter number in blank cell match number on sheet 2 & enter data from adjacent cell
    By Bikeman in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-10-2015, 08:36 PM
  4. [SOLVED] If Cell is blank, enter max date
    By jlo33 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-17-2013, 12:23 AM
  5. [SOLVED] Return alt-enter when enter is pressed in a specific cell
    By bigsteve in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-17-2013, 10:21 AM
  6. Enter data, drop down 1 cell, enter more... etc...
    By Darth269 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-12-2012, 05:21 PM
  7. to enter today's date if a cell is blank
    By Lynn Hanna in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-26-2006, 08:10 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