CODEWITHSUNDEEP
excelexcel-formula
DoubleDC
DoubleDC

Reputation: 13

Excel IF(ISBLANK) but still getting -42716

I'm trying to make a spreadsheet in Excel 2013 where column AF ("Adj 120") has a date, then column AG ("Days Remaining") will give me a number based on AF3-TODAY (for example), but if AF doesn't have a date, then AG will be blank. What I get is -42716.

Here's what I'm using:

=[@[Adj 120]]-TODAY()

Like I said, if I have a date, all is fine, I can conditionally format so positive days are green, and negative days are red. The tricky part is preventing my remaining rows populating "-42716" down the column.

I've used this formula:

=IF(ISBLANK([@[Adj 120]]), "", [@[Adj 120]]-TODAY()

However, is just keeps the formula in the block, no values, literally "=IF(ISBLANK([@[Adj 120]]), "", [@[Adj 120]]-TODAY()" in the block that I've tried using it in. It wouldn't be an issue, but the spreadsheet has several rows that have yet to get to the "Adj 120" column in terms of processing. I have 26 rows, but only six or seven have actually progressed far enough to get an "Adj 120" date.

Upvotes: 1

Views: 80

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

ISBLANK will return false if there is a formula, use this instead:

=IF([@[Adj 120]]="", "", [@[Adj 120]]-TODAY())

Upvotes: 3

Related Questions