Need an IF formula with a date

%3CLINGO-SUB%20id%3D%22lingo-sub-1668790%22%20slang%3D%22en-US%22%3ENeed%20an%20IF%20formula%20with%20a%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1668790%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20looking%20for%20the%20following%20formula%20to%20help%20with%20dates%20and%20deadlines%3A%3C%2FP%3E%3CP%3EIf%20the%20cell%20is%20blank%2C%20write%20%22ASAP%22.%20If%20it's%20not%20blank%2C%20add%205%20years%20to%20the%20date%20in%20the%20cell.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECell%201%20would%20be%3A%201%2F1%2F2005%3C%2FP%3E%3CP%3EIt's%20not%20blank%2C%20so%20Cell%202%20should%20be%3A%201%2F1%2F2010%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20this%20so%20far%2C%20but%20the%20date%20part%20isn't%20right%3A%3C%2FP%3E%3CP%3E%3DIF(ISBLANK(N8)%2C%22ASAP%22%2C%20%22DATE(YEAR(N8)%20%2B%205%2C%20MONTH(N8)%2C%20DAY(N8))%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20suggestions%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1668790%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1669126%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20an%20IF%20formula%20with%20a%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1669126%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F794356%22%20target%3D%22_blank%22%3E%40casino123%3C%2FA%3E%2C%3C%2FP%3E%3CP%3EYou%20were%20soooo%20close...%20Just%20remove%20the%20quotes%20in%20the%20if%20false%20section...%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIF(ISBLANK(N8)%2C%22ASAP%22%2CDATE(YEAR(N8)%2B5%2CMONTH(N8)%2C%20DAY(N8)))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1669143%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20an%20IF%20formula%20with%20a%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1669143%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F703724%22%20target%3D%22_blank%22%3E%40Bennadeau%3C%2FA%3E%26nbsp%3BThanks!%20That%20works%20when%20it's%20blank%2C%20but%20when%20there's%20a%20date%2C%20it%20says%20%22Invalid%20Call%20Reference%20Error.%22%20Do%20you%20know%20what%20that%20means%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1669150%22%20slang%3D%22de-DE%22%3ESubject%3A%20Need%20an%20IF%20formula%20with%20a%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1669150%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F794356%22%20target%3D%22_blank%22%3E%40casino123%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehere%20a%20example%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Datum_berechnung.JPG%22%20style%3D%22width%3A%20689px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F218380iDAA5214C30500394%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Datum_berechnung.JPG%22%20alt%3D%22Formula%20in%20German%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EFormula%20in%20German%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EFormula%20in%20English%3A%3C%2FP%3E%3CP%3E%3DMIN(DATE(YEAR(A2)%2BB2%2CMONTH(A2)%2BC2%2B(DAY(A2%2B1)%3D1)%2B'1%2C0'%2CDAY(A2)*'0%2C1'*(DAY(A2%2B1)%26gt%3B1)))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20happy%20to%20know%20if%20I%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%222%22%3E*%20Kindly%20Mark%20and%20Vote%20this%20reply%20if%20it%20helps%20please%2C%20as%20it%20will%20be%20beneficial%20to%20more%20community%20members%20reading%20here.%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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?

7 Replies

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)))

 

@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?

@casino123 

 

here a example

Formula in GermanFormula 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.

@casino123 

Look at the attached spreadsheet. 

It works fine on my end. I did had to format cell "O8" as date.

@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!

This worked, thank you!!

@Bennadeau Thank you, once I formatted it as a date, it worked great! Thanks!!!