Sep 15 2020 07:25 AM
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?
Sep 15 2020 08:13 AM
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)))
Sep 15 2020 08:16 AM
@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?
Sep 15 2020 08:18 AM
here a example
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.
Sep 15 2020 08:27 AM
Look at the attached spreadsheet.
It works fine on my end. I did had to format cell "O8" as date.
Sep 15 2020 08:39 AM
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!
Sep 15 2020 08:58 AM
@Bennadeau Thank you, once I formatted it as a date, it worked great! Thanks!!!