+ Reply to Thread
Results 1 to 5 of 5

How to use iferror formula on a date field

  1. #1
    Registered User
    Join Date
    08-20-2015
    Location
    Auckland, New Zealand
    MS-Off Ver
    2013
    Posts
    1

    Smile How to use iferror formula on a date field

    Hi,
    I have used an iferror formula on a date field and where the date field is blank in the worksheet I am looking up it has returned 01/01/1900 instead of leaving it blank.
    My formula:=IFERROR(VLOOKUP([@[Batch No.]],'[0. OFM and Solutions MFG Plan.xlsx]Solution MFG Plan'!$A:$D,4,FALSE),0)
    The column that I am putting this formula into is formatted as date format.

    Can someone offer suggestions of how to get this to work.
    Thank you

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How to use iferror formula on a date field

    My simplest suggestion is to keep all the formulas as is and simply suppress display of zero value at all on that particular sheet. This setting is sheet specific, so it only applies the sheet onscreen at the time you set it.

    File > Options > Advanced > scroll down to Display Options for this worksheet: > [ ] show a zero in cells that have zero value (uncheck this)
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: How to use iferror formula on a date field

    You may also consider to apply the custom formatting to the formula cells so that if the formula cell returns a 0, it will show a blank else it will show you the date.

    Use the following custom formatting for your formula cells.

    Please Login or Register  to view this content.
    You may change the dd/mm/yyyy portion as per your requirement.
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  4. #4
    Registered User
    Join Date
    10-17-2018
    Location
    USA
    MS-Off Ver
    MICROSOFT OFFICE PLUS 2010
    Posts
    1

    Re: How to use iferror formula on a date field

    how would I use the "if formula" when I want to create a rule simply stating if the date in one column is prior and/or up to a certain date it shows as "pass", if the dates is post it shows as "fail" example.. columns a and b both have dates.. in column c I apply the rule =if(value date in b1<=a1,"pass","fail").. but it doesn't populate correctly.. if a1 has a date of 9/01/2018, b1 has 9/5/2018, then it should say "pass".. can someone please advise..

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,394

    Re: How to use iferror formula on a date field

    Hi IV4N3O5. Welcome to the forum.

    Unfortunately your post does not comply with Rule 4 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

    As a new member you will not be able to post a link until you have posted at least 10 times. You can ask a Mod to post the link for you or post the title and interested parties can search based upon that.
    Dave

+ 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] PowerPivot: Merge Month Field and Year Field Into a Date Field
    By mo4391 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 07-17-2015, 11:59 AM
  2. [SOLVED] Changing a Text Field with a Date to an Actual Date Field
    By chcalissie in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-22-2014, 09:54 AM
  3. IFERROR - INDEX - DATE RANGE formula issue?
    By domgilberto in forum Excel General
    Replies: 0
    Last Post: 06-10-2014, 12:14 PM
  4. [SOLVED] IFERROR Statement in VBA for date
    By Maxy in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-11-2013, 08:44 AM
  5. IFERROR and DATE
    By Kevalin O in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-16-2013, 05:24 AM
  6. xlfn.IFERROR or IFERROR don't work in Excel 98-2003
    By dj_danu01 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-26-2013, 02:53 AM
  7. Formula help - If condition on a date field
    By gpk_17 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-17-2012, 12:48 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