SOLVED

Days Forumla Help

New Contributor

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")

 

Hannah_Allen_0-1625587222188.png

 

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

 

thanks muchly for all your help!  

4 Replies
best response confirmed by Hannah_Allen (New Contributor)
Solution

@Hannah_Allen 

One way:

=IF(AND(ISNUMBER(AE3),ISNUMBER(AI3)), AI3-AE3, "")
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
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
thank you!!