SOLVED

Days Forumla Help

%3CLINGO-SUB%20id%3D%22lingo-sub-2519976%22%20slang%3D%22en-US%22%3EDays%20Forumla%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2519976%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20day%20all%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20tried%20so%20many%20variations%20and%20I%20just%20can't%20get%20it%20to%20work....I'm%20sure%20I'm%20overthinking%20it...LOL%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20spreadsheet%20I%20created%20to%20track%20our%20service%20calls%20and%20when%20they%20were%20completed%2C%20billed%20and%20paid%20by%20our%20clients.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20first%20one%20I'm%20struggling%20with%20is%20to%20get%20rid%20of%20the%20%23num%20error%20it%20is%20returning%20when%20I%20try%20to%20calculate%20the%20days%20between%20when%20the%20call%20was%20completed%20vs%20when%20we%20billed%20it%20out.%26nbsp%3B%20Ideally%2C%20it%20would%20return%20a%20%220%22.%26nbsp%3B%20I%20have%20tried%3A%3C%2FP%3E%3CP%3E%3Diferror(dateif(ae3%2Cai3%2C%22d%22)%2C0)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eit%20returns%200%20for%20all!!%26nbsp%3B%20Not%20right!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethe%20current%20formula%20entered%20(giving%20me%20the%20%23num%20error%20you%20see%20in%20my%20screen%20shot)%20is%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DDATEDIF('Service%20Tracker%20'!%24AE3%2C'Service%20Tracker%20'!%24AI3%2C%22d%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Hannah_Allen_0-1625587222188.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F293810i655E75EBA71B3F07%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Hannah_Allen_0-1625587222188.png%22%20alt%3D%22Hannah_Allen_0-1625587222188.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOnce%20I%20get%20this%20one%20fixed....then%20the%20next%20issue%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethanks%20muchly%20for%20all%20your%20help!%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2519976%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2520010%22%20slang%3D%22en-US%22%3ERe%3A%20Days%20Forumla%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2520010%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1096844%22%20target%3D%22_blank%22%3E%40Hannah_Allen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOne%20way%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(AND(ISNUMBER(AE3)%2CISNUMBER(AI3))%2C%20AI3-AE3%2C%20%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2520076%22%20slang%3D%22en-US%22%3ERe%3A%20Days%20Forumla%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2520076%22%20slang%3D%22en-US%22%3EYou're%20welcome.%20You%20can%20change%20the%20%22%22%20with%200%20or%20anything%20else.%20I%20suggested%20%22%22%20in%20case%20you%20would%20want%20to%20do%20i.e.%20an%20average%20on%20the%20column%20where%20you%20enter%20the%20formula%3C%2FLINGO-BODY%3E
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!!