Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.

Formula for displaying a date only if another cell isn't empty

Latest post Sun, Mar 8 2015 9:25 AM by PeterG. 3 replies.
  • Sat, Mar 7 2015 12:08 PM

    Formula for displaying a date only if another cell isn't empty

    Hello,

    I'm trying to use this formula =AND(NOT(ISBLANK($S2)),DATE(YEAR(S2)+1,MONTH(S2),DAY(S2))) to display a date one year from the date in cell S2, but only if cell S2 isn't blank. All I receive is True or False in the cell. Please help. Thank you.

    Firehawk583

    Filed under:
    • Post Points: 21
  • Sun, Mar 8 2015 4:27 AM In reply to

    • PeterG
    • Top 10 Contributor
    • Joined on Mon, Feb 25 2008
    • Posts 224
    • Points 3,368

    Re: Formula for displaying a date only if another cell isn't empty

    This formula should do the trick (written for the date cell being A1)

    =IF(AND(TYPE(A1)=1,CODE(CELL("format",A1))=68,A1<>""),DATE(YEAR(A1)+1,MONTH(A1),DAY(A1)),"")

    The AND construct checks that cell A1

    1. contains a number (TYPE)
    2. is formatted as a date (CELL)
    3. is not empty (<>"")

    If all three conditions are met, then the year is incremented by one, or else, the empty string is returned.

    Peter

     

    • Post Points: 21
  • Sun, Mar 8 2015 8:56 AM In reply to

    Re: Formula for displaying a date only if another cell isn't empty

    Thank you Peter. It worked perfectly. Does the =68 mean date? Is there alist of what the formats are by number? Thanks, again, for your help. It's greatly appreciated.

    Filed under:
    • Post Points: 21
  • Sun, Mar 8 2015 9:25 AM In reply to

    • PeterG
    • Top 10 Contributor
    • Joined on Mon, Feb 25 2008
    • Posts 224
    • Points 3,368

    Re: Formula for displaying a date only if another cell isn't empty

    You can check out

    https://support.office.com/en-nz/article/CELL-function-b98b713d-d3de-4148-829f-80c886af6410?ui=en-US&rs=en-NZ&ad=NZ

    There you will see that date and time formats returned by the CELL function all commence with "D". The CODE function simply returns the ASCII code of the first character of the format string.

    Peter

    • Post Points: 5
Page 1 of 1 (4 items) | RSS
Copyright Excel User Group and the relevant contributors, 2010. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.