Dec 01 2022 12:21 PM
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.
Dec 01 2022 12:41 PM - edited Dec 01 2022 12:41 PM
Without seeing the sheet, is the date in K29 after 12/1/2022?
Dec 01 2022 12:44 PM
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...
Dec 01 2022 12:49 PM
Dec 01 2022 12:53 PM
Dec 01 2022 12:57 PM
Dec 01 2022 01:30 PM
Dec 01 2022 01:49 PM
IF(), AND() and TODAY() don't produce #NUM!.
Arithmetic operators and comparison operators don't produce #NUM!.
Leaving DATEDIF(). I rest my case.
Dec 01 2022 01:50 PM
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?
Dec 01 2022 02:15 PM
Dec 01 2022 02:38 PM
Hopefully this will open for you
Dec 01 2022 02:49 PM
No, I cannot access it. Please right-click the workbook on Dropbox and select Dropbox > Copy Dropbox Link from the context menu. Then paste that link into a reply.
Dec 01 2022 02:51 PM
Dec 02 2022 03:15 AM
See @Detlef Lewin's reply.
In the current formula, Dashboard!K14 is after TODAY(), so DATEDIF(Dashboard!K14,TODAY(),"d") returns an error.
Remarks:
Dec 04 2022 12:03 PM
Dec 04 2022 02:59 PM
SolutionI 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.