Days Formula Help - Part 2

%3CLINGO-SUB%20id%3D%22lingo-sub-2520145%22%20slang%3D%22en-US%22%3EDays%20Forumla%20-%20Part%202%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2520145%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20day%20all%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%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%20a%20second%20worksheet%20to%20summarize%20the%20details%20-%20when%20they%20were%20completed%2C%20billed%20and%20paid%20by%20our%20clients.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EData%20Collection%20Worksheet%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_1-1625589751061.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F293815iB8E61B0EA0F802D1%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Hannah_Allen_1-1625589751061.png%22%20alt%3D%22Hannah_Allen_1-1625589751061.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%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%20--%20FIXED!!%26nbsp%3B%20Thank%20you%20%40L%20z%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B...%20formula%20used%20to%20fix%20...%26nbsp%3B%20%3Dif(and(isnumber(AE3)%2C%20isnumber(AI3))%2CAI3-AE3%2C%22%22)%20...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Enext%20part%20....%20the%20formula%20given%20and%20used%20works...however....per%20the%20second%20screen%20shot%20(see%20below)...the%20%23%20of%20days%20(19)%20returned%20IS%20accurate%2C%20however%2C%20it%20is%20only%20accurate%20for%20row%20AE3%20.%26nbsp%3B%20I%20need%20to%20have%20the%20%23%20of%20days%20%2F%20month%20as%20the%20summary%20sheet%20(second%20worksheet%20in%20workbook)%20shows.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethe%20details%20are%20on%20the%20first%20worksheet%20in%20the%20workbook....%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-1625589651422.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F293814i207134418BFE99F6%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Hannah_Allen_0-1625589651422.png%22%20alt%3D%22Hannah_Allen_0-1625589651422.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPretty%20sure%20I%20need%20to%20use%20the%20%22%24%22%20feature%2C%20but%20unsure%20where%2Fhow%2Fif%20true%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%3Eonce%20again....your%20help%20is%20GREATLY%20appreciated!!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2520145%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%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 a second worksheet to summarize the details - when they were completed, billed and paid by our clients.

 

Data Collection Worksheet

 

Hannah_Allen_1-1625589751061.png

 

 

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".  -- FIXED!!  Thank you @L z

 

 ... formula used to fix ...  =if(and(isnumber(AE3), isnumber(AI3)),AI3-AE3,"") ...

 

next part .... the formula given and used works...however....per the second screen shot (see below)...the # of days (19) returned IS accurate, however, it is only accurate for row AE3 .  I need to have the # of days / month as the summary sheet (second worksheet in workbook) shows.

 

the details are on the first worksheet in the workbook....

 

Hannah_Allen_0-1625589651422.png

 

Pretty sure I need to use the "$" feature, but unsure where/how/if true  

 

once again....your help is GREATLY appreciated!!!

0 Replies