Forum Discussion
Solved
#NUM error
Hi,
This formula is returning a #NUM error and I can't see why
=IF(AND(G27+G28<>0,TODAY()>Dashboard!D29+10),DATEDIF(Dashboard!K29,TODAY(),"d")&" Days Overdue",G27+G28)
The error only occurs when looking at 'Dashboard!D29', which is using this formula =IF(D29<>"",D29+13,"") to return a date. I am using the same formulas throughout the sheet with no issue and if I change Dashboard!D29 to D28 or D30 it works (same formulas in these cells). I can't see what is wrong with these formulas when they work perfectly everywhere else.
I have isolated the problem. Using the formula below (which is an example that applies to the whole sheet and is not specific to the cells indicated). =IF(AND(G27+G28<>0,TODAY()>Dashboard!D29+10),DATEDIF(Dashboard!K29,TODAY(),"d")&" Days Overdue",G27+G28)
If
Dashboard!D29+10 is less than Today() and
Dashboard!K29 is only 1 day greater than Today().The formula will return #NUM!
More than 1 day greater than Today() and everything works again.
This means if you were looking at my sheet more than 1 day after the date in Dashboard!K29 you wouldn't have seen the #NUM! error.
If I change ">Dashboard!D29+10" to ">Dashboard!K29" the formula works no matter what the date is.
I can't recall why I decided to use "Dashboard!D29+10", I assume that I wanted the number of workdays not weekdays from the start date even though, although that didn't really affect the end result. Anyway, it all works now.
- Hi Detlef,
Thanks for the link, but it isn't really applicable to this formula. As I have said the formula works for every other date and cell reference, so I don't think the error is because of the DATEDIF function.Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
- I need to clarify that the issue is with D29 AND K29 from the 'Dashboard' tab in the first formula. If I change these to the dates above and below (D/K28 or 30) the formula works.
Does Dashboard!D29 really contain the formula =IF(D29<>"",D29+12,"") ? If so, that would cause a circular reference since the formula refers to its own cell...
- Hi Hans,
It isn't a circular reference as it is saying if D29 is not empty then add 12 to the date that is in D29, otherwise return a blank. The formula is not in D29. it is in K29.
Without seeing the sheet, is the date in K29 after 12/1/2022?
- Hi Patrick,
The date returned is 4/12/22, but I have the same formula returning dates earlier and later with no errors.
