Days Forumla Help

[Hannah_Allen]

Good day all :) 

I have tried so many variations and I just can't get it to work....I'm sure I'm overthinking it...LOL

I have a spreadsheet I created to track our service calls and when they were completed, billed and paid by our clients.

The first one I'm struggling with is to get rid of the #num error it is returning when I try to calculate the days between when the call was completed vs when we billed it out.  Ideally, it would return a "0".  I have tried:

=iferror(dateif(ae3,ai3,"d"),0)

it returns 0 for all!!  Not right!

the current formula entered (giving me the #num error you see in my screen shot) is:

=DATEDIF('Service Tracker '!$AE3,'Service Tracker '!$AI3,"d")

Once I get this one fixed....then the next issue :) 

thanks muchly for all your help!  

[#2 · Accepted solution]

@Hannah_Allen 

One way:

=IF(AND(ISNUMBER(AE3),ISNUMBER(AI3)), AI3-AE3, "")

[#3]

OMG I think that one worked!!!! It gives me blanks instead of 0 but that is OK!!

Thank you
Thank you
Thank you!!!

Next one might be a bit more difficult .... but again, I could also be overthinking ... hahaha....new thread / question coming :)

[#4]

You're welcome. You can change the "" with 0 or anything else. I suggested "" in case you would want to do i.e. an average on the column where you enter the formula

[#5]

thank you!!