Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 22

Thread: 00/Jan/1900

  1. #1
    Board Regular
    Join Date
    Feb 2017
    Posts
    56
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 00/Jan/1900

    Hi,

    I've a formula I'm using to look across a range and pick all the values that match (plenty of googling to achieve this). Unfortunately some cells are blank and therefor return 00/Jan/1900. My current iferror will not remove this 00/Jan/1900 - any ideas?

    Formula:

    =IFERROR(INDEX($B$1:$B$20000,SMALL(IF(ISNUMBER(SEARCH($F$1,$A$1:$A$20000)),MATCH(ROW($A$1:$A$20000),ROW($A$1:$A$20000))) ,ROW(A1))), "")

    If the cell I'm pulling from still has another error I'd still like it to show blank.

    Conditional formatting won't work as I've other formulas dependant on returning the cell blank rather than 00/Jan/1900

    As always thanks

    Stuart

  2. #2
    Board Regular 63falcondude's Avatar
    Join Date
    Jan 2016
    Posts
    3,572
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 00/Jan/1900

    Can you provide a small sample of your data along with the desired output of the formula?

  3. #3
    MrExcel MVP Tetra201's Avatar
    Join Date
    Oct 2016
    Posts
    3,320
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    1 Thread(s)

    Default Re: 00/Jan/1900

    Try this (Note: untested):

    =IFERROR(1/(1/INDEX($B$1:$B$20000,SMALL(IF(ISNUMBER(SEARCH($F$1,$A$1:$A$20000)),MATCH(ROW($A$1:$A$20000),ROW($A$1:$A$20000))),ROW(A1)) )),"")

  4. #4
    Board Regular
    Join Date
    Feb 2017
    Posts
    56
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 00/Jan/1900

    Hi,

    I didn't get the above to work, to be honest maybe my formula is not very good anyway!

    I tried add an image, didn't work. My forum tools isn't working either - annoying.

    Here is a link to an image I uploaded:

    https://www.imageupload.co.uk/image/B6C6
    Last edited by stoothom; Mar 30th, 2017 at 12:44 PM.

  5. #5
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    48,994
    Post Thanks / Like
    Mentioned
    47 Post(s)
    Tagged
    9 Thread(s)

    Default Re: 00/Jan/1900

    Conditional formatting won't work as I've other formulas dependant on returning the cell blank rather than 00/Jan/1900
    What do these other formulas look like?
    I am thinking it may be easier to use Conditional Formatting to hide the zero, and then just update the other formulas to deal with the zeroes.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  6. #6
    Board Regular
    Join Date
    Feb 2017
    Posts
    56
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 00/Jan/1900

    If you click the link you'll see the image.

    Basically I'm returning matching values into cells like F3-F8 *matching F1 to range A2-A8, when it finds a match it returns the start date.

    Example in F3 I have:
    =IFERROR(INDEX($B$1:$B$19998,SMALL(IF(ISNUMBER(SEARCH($F$1,$A$1:$A$19998)),MATCH(ROW($A$1:$A$19998),ROW($A$1:$A$19998))) ,ROW($A1))), "")

    Terrible formula?

    When it finds a blank I don't want it return 00/Jan/1900 like it is in F5

  7. #7
    Board Regular
    Join Date
    Feb 2017
    Posts
    56
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 00/Jan/1900

    Can you just hide 0's using conditional formating? i.e. not make the text blank or something similar.

  8. #8
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    48,994
    Post Thanks / Like
    Mentioned
    47 Post(s)
    Tagged
    9 Thread(s)

    Default Re: 00/Jan/1900

    If you click the link you'll see the image.
    Unfortunately, no I won't. My work's security processes block those sites.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  9. #9
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    48,994
    Post Thanks / Like
    Mentioned
    47 Post(s)
    Tagged
    9 Thread(s)

    Default Re: 00/Jan/1900

    Can you just hide 0's using conditional formating? i.e. not make the text blank or something similar.
    That is what we were asking, but in your original post you said:
    Conditional formatting won't work as I've other formulas dependant on returning the cell blank rather than 00/Jan/1900
    which is why I asked:
    What do these other formulas look like?
    I am thinking it may be easier to use Conditional Formatting to hide the zero, and then just update the other formulas to deal with the zeroes.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  10. #10
    Board Regular
    Join Date
    Feb 2017
    Posts
    56
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 00/Jan/1900

    Ah,

    I'm sorry. I've no idea why my excel add in isn't working to make this easier.

    Basically the formula dependant on good dates (not 00/Jan/1900) is:
    =IFERROR(SMALL(F3:F8,1),"")

    You probably know but that formula grabs the smallest date from the range.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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