#Error! Issue

[googleworkspace]

#Error! Issue

[cryingryan]

I can't reproduce your error but maybe try this:

=IF(ISBLANK(E2),"",DATESUBTRACT(text(DATEADD(E2,"Years",3),"MM/dd/yyy"),"Days",1))

Make sure the column/text is formatted to be a date.

Just an idea.

[jprobst24]

Still no improvement.
I checked formating and replaced formulas with the one you suggested.
Maybe it is coincidental, but the problem seemed to show up after my list exceeded 20 entries. I currently have 58.

[jprobst24]

Here is an example. Although this one functions correctly, even though it is exactly the same data as the original file, minus the personal identifiable information.

https://docs.google.com/spreadsheets/d/1wWbP9QEBNjTUe_fM1b6Gt9_mUJekBl0mkF9IGWCffmo/edit?usp=sharing

[cryingryan]

I figured this was for EC (Special Ed) services - I'm a high school administrator.

So even in the example you sent me, it is all loading fine. I was able to reproduce a similar error when I fed in a date formatted as a date value as the first parameter in DATESUBTRACT.

Maybe try this: =IF(ISBLANK(E2),"",DATESUBTRACT(TO_DATE(DATEADD(E2,"Years",3)),"Days",1))

It should ensure that whatever is being fed into the 1st parameter is treated as a date in the format MM/dd/yyy.

[Sabotnik]

What is the purpose of the script? I'm not terribly familiar with Google Sheets and formulas and it's been awhile since I've messed with Excel formulas beyond simple sums, etc.

If the E column is blank, what is the date subtracted/added from? i.e. what is the DATESUBTRACT(DATEADD(E2,"Years",3),"Days",1)) part of your script supposed to do?

My guess is that there are some cases or inflexibility with this, which is why it is failing sometimes and succeeding other times. I saw your post on Stack Overflow - if you can shed some light on this, I may be able to help, otherwise I would suggest trying to solve the problem in a different way. Usually, it helps me to write out in plain English what I'm trying to accomplish, figure out ways to break it and then once I cannot break it anymore, that's the best solution at the time.

If I know what you are trying to accomplish exactly, I may be able to look and offer a solution or 2 after some research.

[cryingryan]

A reevaluation must occur within 3 years of the previous evaluation. /u/jprobst24 is added three years and subtracting a day to make sure his team meets the reevaluation deadline.

If you don't want a Google Script solution, try this: =date(year(E2)+3,month(E2),day(E2)-1).

Also, I like your conditional formatting. This will be a great tool for your team.

[erickoledadevrel]

I'm also not able to reproduce this problem. There can be problems when executing a lot of custom functions on the same spreadsheet. One workaround is to use custom functions that operate on ranges of data instead of individual cells. The DATEADD and DATESUBTRACT functions have recently been extended to work on ranges, so you might want to try that.

[jprobst24]

Sorry for the delayed responses:
I was still having the same issue with cryingryan's idea:
=IF(ISBLANK(E2),"",DATESUBTRACT(TO_DATE(DATEADD(E2,"Years",3)),"Days",1))

so instead I tried his second idea:
=date(year(E2)+3,month(E2),day(E2)-1).

but changed it to:
=IF(ISBLANK(E2),"",date(year(E2)+3,month(E2),day(E2)-1))

This was weird because originally the DATESUBTRACT method worked fine, and it would work again on a new sheet, but not in the original.

erickoledadevrel, I am curious - how would I use DATEADD and DATESUBTRACT functions to work on ranges? In the past I was building the fomula in F2, and then using copy down to extend it into the rest of the column.

[erickoledadevrel]

To make it work on ranges, just pass in a range of values instead of a single cell:

=DATEADD(E2:E100, "Years", 3)

[juanmafx]

I'm also not able to reproduce this problem to . There can be problems in the setings.
Long before..