+ Reply to Thread
Results 1 to 10 of 10

Excel 2007 : changing a defualt date (00/00/1900) to a blank

  1. #1
    Forum Contributor
    Join Date
    08-26-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2007
    Posts
    149

    changing a defualt date (00/00/1900) to a blank

    Hi All,

    I am trying to automate a spreadsheet that tells me when an item had an action taken out on it, and when in the future the next action is due on it.

    this data is ran from a database system, which I extract and pop into a spread sheet which collates what I need. But when no data appears on the Data, my formula brings that back as a default date (00/00/1900), which makes it all hard to work out.

    Is there something I can add to my formula, or is there another formula that would take out the default date and leave as a blank?

    I have used the following formula:

    Index(raw data!A:A,match(raw data!G:G,Ouput!A:A,false),1)

    I have attached a dummy example for you to play with.

    Looking forward to your fantastic as always responses.

    Cheers

    Donna
    Attached Files Attached Files

  2. #2
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: changing a defualt date (00/00/1900) to a blank

    Hi Dona

    Try this in B1

    =IF(INDEX('Raw Data'!$B$2:$B$76;MATCH('Raw Data'!$G$2:$G$76;Output!$A$2:$A$76;FALSE);1)="";"";INDEX('Raw Data'!$B$2:$B$76;MATCH('Raw Data'!$G$2:$G$76;Output!$A$2:$A$76;FALSE);1))


    Hope to helps you.
    Attached Files Attached Files
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Forum Contributor
    Join Date
    08-26-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2007
    Posts
    149

    Re: changing a defualt date (00/00/1900) to a blank

    As always Brillian Fotis1991.

    can I add a iserror or isna after the if but before the index, for the instances where it pulls back #n/a's?

    Cheers

    Donna

  4. #4
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: changing a defualt date (00/00/1900) to a blank

    Thank for your good words, Dona. Glad to helps you.

    As you are in Excel 2007, I think it's better to go with "ISERROR".

    But as i am in Excel 2003, i can not test it. At least, not Today. All computers near me, use Excel 2003...

  5. #5
    Forum Contributor
    Join Date
    08-26-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2007
    Posts
    149

    Re: changing a defualt date (00/00/1900) to a blank

    No worries Fotis, if you could try it when you are around an excel 2007, that would be great, as i have added iserror to the beginning of the formula, and changed the #n/a to True!!!

    Look forward to hearing from you soon

    Donna

  6. #6
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: changing a defualt date (00/00/1900) to a blank

    Hi

    I had,(a little ago), for ten minutes a laptop with Excel 2007.

    As, i am not familiar with this(many years in 2003), i did this formula and as i tested, it works. Maybe, there is something shorter....

    In G2 and copy down.

    =IF(INDEX('Raw Data'!$F$2:$F$76;MATCH('Raw Data'!$G$2:$G$76;Output!$A$2:$A$76;FALSE);1)="";"";IFERROR(INDEX('Raw Data'!$F$2:$F$76;MATCH('Raw Data'!$G$2:$G$76;Output!$A$2:$A$76;FALSE);1);""))
    Hope to helps you.

  7. #7
    Forum Contributor
    Join Date
    08-26-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2007
    Posts
    149

    Re: changing a defualt date (00/00/1900) to a blank

    Hi fotis,

    i have doen this, adn my #N/a's are still there!!!

    I was actually putting my iferror/iserror at the start of the whole formula, not in the second part, whicch makes a lot more sense!!!


    Cheers

    Donna

  8. #8
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: changing a defualt date (00/00/1900) to a blank

    Hi Donna.

    Did you try this, as i gave you(in the second part)?

    I can give you a solution in excel 2003, but will need one more click, each time.

    If you don't find a solution with this and if you like, we can do it.

    And pls, in this case, upload a sample that shows the problem.

  9. #9
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: changing a defualt date (00/00/1900) to a blank

    I think you can fix the original problem (dates displaying as 00/00/1900) with just a change to the cell format. Use this custom fomat

    dd/mm/yyyy;;

    using the two semi-colons at the end makes the "zero dates" display as blank rather than 00/00/1900

    Now you can just use a simple IFERROR wrapped around your original INDEX/MATCH, e.g. this formula in B2 copied across and down

    =IFERROR(INDEX('Raw Data'!A$2:A$76,MATCH($A2,'Raw Data'!$G$2:$G$76,0)),0)

    [note: I changed that a little]

    see attached ( changed A11 to demonstrate)
    Attached Files Attached Files
    Audere est facere

  10. #10
    Forum Contributor
    Join Date
    08-26-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2007
    Posts
    149

    Re: changing a defualt date (00/00/1900) to a blank

    Yes Fotis I tried both parts, still no luck,

    BUT

    Daddylonglegs, yours worked, thankyou very much, much appreciated.

    i know have come accross another problem!!! I have just noticed that in my data I have double/treble ups! AARRGGHHH!!

    Is there a way of asking this formula to select the latest date available to bring back for each unique.

    i.e on the attached example the uniques has 3 entries, 2 dates for inspection A and 1 for Inspection B, A has 1 date from last year, and 1 for this year, and B has a date from last year.

    I need the most recent date from each inpection to appear in one line, so i can work out when to book in the next one.

    As you can see, I get 3 Inspection A dates, all 3 of them being last years date, not he most recent available, and no dates for inspection B of which in the Data sheet there is one.

    I hope this makes sense.

    looking forward to your advice.

    Cheers

    Donna
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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