+ Reply to Thread
Results 1 to 5 of 5

I need my formula to return nothing rather than 00-Jan-00

  1. #1
    Forum Contributor
    Join Date
    02-09-2011
    Location
    Perth, Australia
    MS-Off Ver
    2016
    Posts
    161

    I need my formula to return nothing rather than 00-Jan-00

    Hi,

    This formula Determines if “A” is present anywhere in the range R6:R303 and returns the value (a date) in cell B2 if it is.

    If A is not present it returns the earliest date.

    =IF(COUNTIF(R6:R303,"*"&"A"&"*"),$B$2,MIN(R6:R303))

    The problem is if there is no data at all in the range it returns 00-Jan-00.

    How can I modify the formula so that it returns nothing if the range is empty (except for formulas)?

    Thanks very much

    Dave

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: I need my formula to return nothing rather than 00-Jan-00

    Try:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: I need my formula to return nothing rather than 00-Jan-00

    Or:

    =IFERROR(1/(1/IF(COUNTIF(R6:R303,"*"&"A"&"*"),$B$2,MIN(R6:R303))),"")

  4. #4
    Forum Contributor
    Join Date
    02-09-2011
    Location
    Perth, Australia
    MS-Off Ver
    2016
    Posts
    161

    Re: I need my formula to return nothing rather than 00-Jan-00

    Thanks very much guys

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: I need my formula to return nothing rather than 00-Jan-00

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.

+ 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: 07-17-2015, 03:33 AM
  2. Statement to return formula to the next row down, if formula's result if false?
    By AlphaRaveNZ in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-12-2015, 10:54 PM
  3. Statement to return formula to the next row down, if formula's result if false?
    By AlphaRaveNZ in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-12-2015, 10:54 PM
  4. Formula for: IF B1 = a range1 then return X, if range2 then return Y...
    By taylorjkeen in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-09-2015, 06:02 PM
  5. Replies: 3
    Last Post: 01-28-2014, 03:10 PM
  6. [SOLVED] Formula to always return cell below what a vlookup would return
    By KCHEXCEL in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-28-2013, 12:57 AM
  7. [SOLVED] IF statement to return 0 if 0 in cell if not return formula
    By CraigieL in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 07-15-2013, 09:46 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