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
https://docs.google.com/spreadsheets/d/1e_EIA-H51Ue5Cs2CXRM4a8mpfR8f_45m/edit?usp=share_link&ouid=110027759666753411685&rtpof=true&sd=true
HansVogelaar's avatar
HansVogelaar
MVP
Dec 02, 2022

Emily3918 

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.

Resources