Forum Discussion

Emily3918's avatar
Emily3918
Copper Contributor
Dec 01, 2022
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 l...
excel
  • Emily3918's avatar
    Emily3918
    Dec 04, 2022

    HansVogelaar 

    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.

Emily3918's avatar
Emily3918
Copper Contributor
Dec 01, 2022
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.

Resources