+ Reply to Thread
Results 1 to 8 of 8

Writing a formula that will return blanks for certain periods...

  1. #1
    Registered User
    Join Date
    08-20-2015
    Location
    Raleigh, NC
    MS-Off Ver
    2013
    Posts
    24

    Writing a formula that will return blanks for certain periods...

    I have a data sheet that has a 13 month break in data that I need to write a formula for. I need it to return "" if the corresponding cell A is blank and I also need to add in that if the date is greater than June 30, 2012 and less than September 30, 2013 then it will return "". Here is what I have, the part in red is the portion I'm having issues with.

    =IF($A5="","",If(and($A5>date(2012,6,30),$A5<(2013,9,30))),"",IF(ISERROR(VLOOKUP($A5,Data!$A:$BR,2,0)),0,VLOOKUP($A5,Data!$A:$BR,2,0)))

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,270

    Re: Writing a formula that will return blanks for certain periods...

    You are probably having trouble because it's not really a "date" Date's are numbers in Excel. In a blank cell, type = ISNUMBER(A5) to see what happens. What format are your dates in?
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    08-20-2015
    Location
    Raleigh, NC
    MS-Off Ver
    2013
    Posts
    24

    Re: Writing a formula that will return blanks for certain periods...

    The dates are in the format "Jul-12". I did the IsNumber and it returned "true".

    I changed one of them to a more traditional mm/dd/yyyy date and it still give the "formula contained an error" message.

  4. #4
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Writing a formula that will return blanks for certain periods...

    You've got a misplaced ) in there
    also missing the 2nd date function
    =IF($A5="","",If(and($A5>date(2012,6,30),$A5<(2013,9,30))),"",IF(ISERROR(VLOOKUP($A5,Data!$A:$BR,2,0)),0,VLOOKUP($A5,Data!$A:$BR,2,0)))

    Should be
    =IF($A5="","",If(and($A5>date(2012,6,30),$A5<date(2013,9,30)),"",IF(ISERROR(VLOOKUP($A5,Data!$A:$BR,2,0)),0,VLOOKUP($A5,Data!$A:$BR,2,0))))
    Last edited by Jonmo1; 09-28-2015 at 05:09 PM.

  5. #5
    Registered User
    Join Date
    08-20-2015
    Location
    Raleigh, NC
    MS-Off Ver
    2013
    Posts
    24

    Re: Writing a formula that will return blanks for certain periods...

    Still giving me the error message

  6. #6
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Writing a formula that will return blanks for certain periods...

    Refresh, I found other issues after my initial post. And edited it to reflect..

  7. #7
    Registered User
    Join Date
    08-20-2015
    Location
    Raleigh, NC
    MS-Off Ver
    2013
    Posts
    24

    Re: Writing a formula that will return blanks for certain periods...

    Thanks, that worked. Don't know how I missed that but appreciate the help.

  8. #8
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Writing a formula that will return blanks for certain periods...

    You're welcome.

+ 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. Return most common text from column cells with formula while ignoring blanks
    By rosco01995 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-29-2014, 03:55 AM
  2. [SOLVED] Array Formula - Return blanks instead of zero
    By rogelioperrett in forum Outlook Formatting & Functions
    Replies: 2
    Last Post: 10-15-2014, 10:00 AM
  3. Replies: 1
    Last Post: 05-02-2012, 08:05 AM
  4. Remove periods and/or blanks from the end of a cell
    By Rabid Squirrel in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-02-2011, 05:48 PM
  5. Writing blanks spaces in a .txt outputfile from VBA
    By Lemming75 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-28-2008, 09:07 AM
  6. writing a formula to return non-empty cells
    By pmetzak in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-18-2007, 03:34 PM
  7. Form writing blanks!
    By Form problem! in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-02-2005, 07:06 PM

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