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.
- 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?
Hopefully this will open for you
https://www.dropbox.com/pri/get/Emily%20Read/%23NUM%20ERROR.xlsx?_subject_uid=4694101600&w=AAAfydQ56u1F_1cT1QJeAo-Y0aDmMqzg6-tfaSh2P3PIEw
IF(), AND() and TODAY() don't produce #NUM!.
Arithmetic operators and comparison operators don't produce #NUM!.
Leaving DATEDIF(). I rest my case.
- Except that I have over 100 formulas exactly the same that are not producing #NUM, so thanks but this is not the solution.
