+ Reply to Thread
Results 1 to 10 of 10

Date formula to Return blank if cell is empty

  1. #1
    Registered User
    Join Date
    09-30-2008
    Location
    Arizona
    Posts
    3

    Date formula to Return blank if cell is empty

    Trying to answer a question, right now i have a formula that calculates 12 months on to a prior field, IE:=DATE(YEAR(C6),MONTH(C6)+12,DAY(C6)), when I type this into cell (D6)...if there is nothing in cell (C6), the date formats to 12/31/1900....once i type something into C6, the date fills in to the proper date, is there a formula, or a way to format this cell so if nothing is in the cell C6, D6 shows nothing in the field.
    Last edited by VBA Noob; 09-30-2008 at 05:35 PM.

  2. #2
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Ctaxes,

    Welcome to the forum. Please read forum rules below before posting again.

    Your title has being amended for you this time

    =IF(C6="","",DATE(YEAR(C6),MONTH(C6)+12,DAY(C6)))
    VBA Noob
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!

  3. #3
    Forum Contributor mewingkitty's Avatar
    Join Date
    09-29-2008
    Location
    Fort McMurray, Alberta, Canada
    MS-Off Ver
    Excel 2003
    Posts
    949

    Yes, yes there is.

    I've used
    Please Login or Register  to view this content.
    That way if c6 is blank, your formula cell outputs a "" which is nothing, so it stays blank. with the formula after that, since that is the "In case of false" statement, then it will carry out your formula.

    Same thing as what he said, I like the isblank statement because it's clear what I've done later on, and I'm good at forgetting things.

    Cheers.
    Last edited by mewingkitty; 09-30-2008 at 09:07 PM.

  4. #4
    Registered User
    Join Date
    09-30-2008
    Location
    Arizona
    Posts
    3

    Date formula to Return blank if cell is empty

    I posted the formula you responded with, testing the formula in my cell, the cell however will return blank even if information is in the cell, C6 is suppose to show a date i enter manually, IE: 1/22/2008, in D6 the desired effect, is suppose to be 1/22/2009, unless there is nothing entered into the cel C6, then I wish D6 to show empty, even with the correct formula in the cell.

    IE:
    C6____________D6
    1/22/2008______formula[ =DATE(YEAR(C6),MONTH(C6)+12,DAY(C6)) ] which will show 1/22/2009.

    If there is no date in C6 it shows the date 12/31/1900 in cell D6

    IE:
    C6___________D6
    *blank*_______formula[*same as above*] which shows 12/31/1900

  5. #5
    Registered User
    Join Date
    09-30-2008
    Location
    Arizona
    Posts
    3
    I just used your formula Mewingkitty, I got the desired effect and the formatting sticks! I appreciate all the help, a problem solved! I do apoligize if I am not posting my issues correctly, practice will make perfect!

  6. #6
    Forum Contributor mewingkitty's Avatar
    Join Date
    09-29-2008
    Location
    Fort McMurray, Alberta, Canada
    MS-Off Ver
    Excel 2003
    Posts
    949

    Sorries

    Forgot to wrap code when I posted that first link.
    I'll keep an eye on it in the future, I'm new here, sorry for the breach in policy.
    Last edited by VBA Noob; 10-01-2008 at 12:27 AM.

  7. #7
    Registered User
    Join Date
    02-14-2009
    Location
    Bristol, England
    MS-Off Ver
    Excel 2010
    Posts
    9

    Exclamation A null string is not the same as a blank

    Quote Originally Posted by mewingkitty View Post
    I've used
    Please Login or Register  to view this content.
    That way if c6 is blank, your formula cell outputs a "" which is nothing, so it stays blank. with the formula after that, since that is the "In case of false" statement, then it will carry out your formula.

    Same thing as what he said, I like the isblank statement because it's clear what I've done later on, and I'm good at forgetting things.

    Cheers.
    Apologies for reopening this thread, but I feel it should be pointed out that the above two solutions are, technically speaking, incorrect. A cell containing a null string "" is not the same as a blank cell. This technicality is important and should not be ignored, as formulas assessing blanks will return unexpected results and hence risk introducing more bugs into the workbook. For example, most blatantly the ISBLANK(value) function will return FALSE for a cell containing a null string.

    However, workarounds do exist and the following links should point you in the right direction:
    http://stackoverflow.com/questions/1...rmula-in-excel
    http://excel.tips.net/T002814_Return...ank_Value.html
    Last edited by tunaaa; 09-17-2011 at 12:50 PM.

  8. #8
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    10,574

    Angry Re: Date formula to Return blank if cell is empty

    @tunaaa:
    Posts like yours are the reason why the forum needs an automatic archiver to lock/close old threads.
    Ben Van Johnson

  9. #9
    Registered User
    Join Date
    10-30-2012
    Location
    paradise
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: Date formula to Return blank if cell is empty

    Hello everyone - I have a simple formula that is counting the days when a date is entered in a cell however how do I have the cell blank when no date is in the cell?
    right now I have a bunch of numbers (41646 41646) for example all the way down.

    I know I have to add a " " somewhere but dont know where!! please help!!
    my formula is below:

    =DATEDIF(M4,TODAY(),"d")

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    48,344

    Re: Date formula to Return blank if cell is empty

    marquint, hi and welcome to the forum

    Unfortunately your post does not comply with Rule 2 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.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ 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