Forum Discussion

casino123's avatar
casino123
Copper Contributor
Sep 15, 2020

Need an IF formula with a date

I'm looking for the following formula to help with dates and deadlines:

If the cell is blank, write "ASAP". If it's not blank, add 5 years to the date in the cell. 

 

Cell 1 would be: 1/1/2005

It's not blank, so Cell 2 should be: 1/1/2010

 

I have this so far, but the date part isn't right:

=IF(ISBLANK(N8),"ASAP", "DATE(YEAR(N8) + 5, MONTH(N8), DAY(N8))")

 

Any suggestions?

excel
Formulas and Functions

7 Replies

  • himanshu_nassa's avatar
    himanshu_nassa
    Copper Contributor
    Sep 15, 2020

    casino123 

    Use the EDATE formula as below:

     

    =IF(ISBLANK(B2), "ASAP", EDATE(B2,60))

     

    The argument in EDATE needs to be the start date (B2) and the number of months you want to offset.

     

    Please mark this as a reply if it helps!

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Sep 15, 2020

    casino123 

     

    here a example

    Formula in German

    Formula in English:

    =MIN(DATE(YEAR(A2)+B2,MONTH(A2)+C2+(DAY(A2+1)=1)+{1,0},DAY(A2)*{0,1}*(DAY(A2+1)>1)))

     

     

    I would be happy to know if I could help.

     

     

    Nikolino

    I know I don't know anything (Socrates)

     

    * Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

  • Bennadeau's avatar
    Bennadeau
    Iron Contributor
    Sep 15, 2020

    Hi casino123,

    You were soooo close... Just remove the quotes in the if false section...

    =IF(ISBLANK(N8),"ASAP",DATE(YEAR(N8)+5,MONTH(N8), DAY(N8)))

     

    • casino123's avatar
      casino123
      Copper Contributor
      Sep 15, 2020

      Bennadeau Thanks! That works when it's blank, but when there's a date, it says "Invalid Call Reference Error." Do you know what that means?

Resources