SOLVED

need to get date gap

%3CLINGO-SUB%20id%3D%22lingo-sub-1422858%22%20slang%3D%22en-US%22%3Eneed%20to%20get%20date%20gap%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1422858%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehere%20with%20my%20excel.%20i%20need%20to%20get%20date%20gap%20for%20highlighted%20column.%20formula%20only%20working%20with%201st%20cell%20others%20shows%20wrong%20value%2C%20can%20anyone%20look%20in%20to%20my%20excel%20and%20help%20me%20soon.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1422858%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAdmin%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EUser%20Adoption%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1422904%22%20slang%3D%22en-US%22%3ERe%3A%20need%20to%20get%20date%20gap%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1422904%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F329467%22%20target%3D%22_blank%22%3E%40Ravindu94%3C%2FA%3E%26nbsp%3BFirst%20of%20all%20you%20need%20to%20change%20G1%20in%20the%20formula%20that%20calculated%20the%20%22date%20gap%22%20to%20%24G%241%20and%20most%20%22dates%22%20in%20column%20C%20are%20not%20real%20dates.%20Deducting%20a%20text%20from%20the%20date%20in%20G1%20returns%20an%20error.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUsed%20Text-to-columns%20to%20make%20real%20dates.%20See%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1423080%22%20slang%3D%22en-US%22%3ERe%3A%20need%20to%20get%20date%20gap%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1423080%22%20slang%3D%22en-US%22%3Ethis%20is%20really%20useful%3CBR%20%2F%3Ethank%20you%20very%20much%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1423129%22%20slang%3D%22en-US%22%3ERe%3A%20need%20to%20get%20date%20gap%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1423129%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F329467%22%20target%3D%22_blank%22%3E%40Ravindu94%3C%2FA%3E%26nbsp%3BYou're%20welcome!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Contributor

Hi,

 

here with my excel. i need to get date gap for highlighted column. formula only working with 1st cell others shows wrong value, can anyone look in to my excel and help me soon.

3 Replies
Highlighted
Best Response confirmed by Ravindu94 (Contributor)
Solution

@Ravindu94 First of all you need to change G1 in the formula that calculated the "date gap" to $G$1 and most "dates" in column C are not real dates. Deducting a text from the date in G1 returns an error.

 

Used Text-to-columns to make real dates. See attached.

Highlighted
this is really useful
thank you very much
Highlighted

@Ravindu94 You're welcome!