Forum Discussion
#NUM error
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.
See Detlef_Lewin's reply.
In the current formula, Dashboard!K14 is after TODAY(), so DATEDIF(Dashboard!K14,TODAY(),"d") returns an error.
Remarks:
- Dashboard!K29 does not contain the formula =IF(D29<>"",D29+13,"") as you claimed (which would cause a circular reference), but =IF(D28<>"",D28+13,"") which is perfectly fine.
- Instead of DATEDIF(Dashboard!K14,TODAY(),"d") you can use the simpler TODAY()-Dashboard!K14. If you had used that, you would have seen that the number of overdue days became negative.
I have unmarked my reply.
- No offence to Hans, but I marked this as the best response by mistake. If anyone does have an issue similar to this, I solved it further down the thread.
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.My remarks were based on your statement in the first post
"The error only occurs when looking at 'Dashboard!D29', which is using this formula =IF(D29<>"",D29+13,"") "
- I appreciate the time that some of you have put into my query, but I don't think that any of you have understood the issue.
Your remarks do not make any sense, K29 does contain the formula =IF(D29<>"",D29+13,""), I copied and pasted it out of that cell, maybe you were looking in the cell K28 which does have the formula =IF(D28<>"",D28+13,""). It isn't circular, it is a fairly basic IF statement that works in all other cells and did not produce #NUM anywhere else.
In the spread sheet that I attached the cell with #NUM was no longer in referring to K29 as I had removed some lines and columns, it was referring to K14, which I had highlighted red. Maybe I should have pointed this out.
All the remarks seem to miss the obvious point in that these formulas are working in every other cell except for this one, if they were circular then why was #NUM not occurring in all of them?
While the suggested formula of Today()-Dashboard!K14 also works the issue appears to have resolved itself and is not occurring elsewhere. If it does come up again, I will try this alternative, but as it is I will not be requiring any more advice on this matter. Thankyou.