Home

Excel doesnot recognize the date until duble click

%3CLINGO-SUB%20id%3D%22lingo-sub-37634%22%20slang%3D%22en-US%22%3EExcel%20doesnot%20recognize%20the%20date%20until%20duble%20click%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-37634%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20problem%20in%20Excel%20%2C%20I%20copied%20dates%20from%20internet%20report%20to%20Excel%20but%20Excel%20does%20not%20recognize%20them%20as%20date%20till%20I%20press%20double%20click%20and%20Enter%20in%20each%20cell%20of%20the%20date%20column%3C%2FP%3E%3CP%3EI%20tried%20many%20ways%20I%20found%20in%20the%20internet%20%2Cbut%20no%20one%20of%20them%20was%20usefull%20for%20me.%3C%2FP%3E%3CP%3Ethe%20ways%20which%20I%20tried%20%22%20copy%20and%20paste%20special%22%20%26amp%3B%20%22Text%20to%20columns%20%22%3C%2FP%3E%3CP%3Ethe%20format%20in%20the%20cells%20is%20correct%20%2Cso%20I%20do%20not%20know%20how%20to%20fix%20it%20because%20the%20data%20is%20so%20big%20in%20the%20column%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-37634%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-758121%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20doesnot%20recognize%20the%20date%20until%20duble%20click%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-758121%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20work%20perfectly.%20Thank-you%20so%20much!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-38209%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20doesnot%20recognize%20the%20date%20until%20duble%20click%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-38209%22%20slang%3D%22en-US%22%3E%3CP%3ENot%20exactly%20sure%20how%20to%20resolve%20it.%20%26nbsp%3B%20You%20can%20start%20by%20seeing%20if%20there's%20any%20obvious%20issues%20in%20your%20system%20%2F%20region%20settings%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%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F9739iBF4ABEF4033DEACD%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Region%20Settings.PNG%22%20title%3D%22Region%20Settings.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-38187%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20doesnot%20recognize%20the%20date%20until%20duble%20click%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-38187%22%20slang%3D%22en-US%22%3E%3CP%3Eso%20%2C%20is%20there%20any%20way%20to%20solve%20the%20issue%20%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-37829%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20doesnot%20recognize%20the%20date%20until%20duble%20click%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-37829%22%20slang%3D%22en-US%22%3E%3CP%3ESorry%20Abdullah%2C%20I%20missed%20that%20file%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E's%20answer%20may%20be%20the%20key%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-37821%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20doesnot%20recognize%20the%20date%20until%20duble%20click%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-37821%22%20slang%3D%22en-US%22%3E%3CP%3EIMHO%2C%20the%20trick%20which%20Wyn%20mentioned%20shall%20work%20if%20the%20date%20format%20in%20regional%20settings%20is%20the%20same%20as%20text%20representation%20of%20dates%20you%20entered.%20Other%20words%2C%20Hijri%20system%20calendar%20plus%20office%20package%20with%20Arabic%20SKU.%20But%20that%20only%20my%20guess%2C%20i'm%20in%20Georgian%20calendar%20and%20can't%20reproduce%20the%20issue...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-37818%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20doesnot%20recognize%20the%20date%20until%20duble%20click%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-37818%22%20slang%3D%22en-US%22%3E%3CP%3EI%20already%20attached%20file%20in%20the%20first%20post%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-37778%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20doesnot%20recognize%20the%20date%20until%20duble%20click%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-37778%22%20slang%3D%22en-US%22%3E%3CP%3Eare%20you%20able%20to%20attach%20a%20file%20containing%20the%20problematic%20column%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-37776%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20doesnot%20recognize%20the%20date%20until%20duble%20click%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-37776%22%20slang%3D%22en-US%22%3E%3CP%3EI%20tried%20these%20%2CUnfortunately%20they%20are%20not%20working%20too%20%2C%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-37775%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20doesnot%20recognize%20the%20date%20until%20duble%20click%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-37775%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Abdullah%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOne%20possible%20solution%20is%20to%20highlight%20the%20column%20of%20dates%20and%20then%20select%20Data%20%26gt%3B%20Text%20to%20columns%20%26gt%3B%20Finish%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20%22tricks%22%20Excel%20into%20treating%20text%20as%20numbers%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlternatively%20copy%20a%20blank%20cell%20then%20highlight%20the%20dates%20and%20Paste%20Special%20%26gt%3B%20Add%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20again%20tricks%20Excel%20into%20adding%200%20to%20each%20cell%20and%20therefore%20the%20text%20gets%20converted%20to%20values%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-758130%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20doesnot%20recognize%20the%20date%20until%20duble%20click%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-758130%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3BThis%20worked%20perfectly!%20Thank-you%20so%20much.%20You%20saved%20me%20hours%20and%20tons%20of%20frustration!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-758131%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20doesnot%20recognize%20the%20date%20until%20duble%20click%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-758131%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20worked%20perfectly!%20Thank-you%20so%20much.%20You%20saved%20me%20hours%20and%20tons%20of%20frustration!%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-758132%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20doesnot%20recognize%20the%20date%20until%20duble%20click%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-758132%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3B%3CSPAN%3EThis%20worked%20perfectly!%20Thank-you%20so%20much.%20You%20saved%20me%20hours%20and%20tons%20of%20frustration!%3C%2FSPAN%3E%3C%2FP%3E%3CBLOCKQUOTE%3E%3CHR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3Bwrote%3A%3CBR%20%2F%3E%3CP%3EHi%20Abdullah%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOne%20possible%20solution%20is%20to%20highlight%20the%20column%20of%20dates%20and%20then%20select%20Data%20%26gt%3B%20Text%20to%20columns%20%26gt%3B%20Finish%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20%22tricks%22%20Excel%20into%20treating%20text%20as%20numbers%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlternatively%20copy%20a%20blank%20cell%20then%20highlight%20the%20dates%20and%20Paste%20Special%20%26gt%3B%20Add%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20again%20tricks%20Excel%20into%20adding%200%20to%20each%20cell%20and%20therefore%20the%20text%20gets%20converted%20to%20values%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CHR%20%2F%3E%3C%2FBLOCKQUOTE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Abdullah Omam
Occasional Contributor

I have problem in Excel , I copied dates from internet report to Excel but Excel does not recognize them as date till I press double click and Enter in each cell of the date column

I tried many ways I found in the internet ,but no one of them was usefull for me.

the ways which I tried " copy and paste special" & "Text to columns "

the format in the cells is correct ,so I do not know how to fix it because the data is so big in the column

9 Replies

Hi Abdullah

 

One possible solution is to highlight the column of dates and then select Data > Text to columns > Finish

 

This "tricks" Excel into treating text as numbers

 

Alternatively copy a blank cell then highlight the dates and Paste Special > Add  

 

This again tricks Excel into adding 0 to each cell and therefore the text gets converted to values

 

I tried these ,Unfortunately they are not working too ,

are you able to attach a file containing the problematic column?

I already attached file in the first post

IMHO, the trick which Wyn mentioned shall work if the date format in regional settings is the same as text representation of dates you entered. Other words, Hijri system calendar plus office package with Arabic SKU. But that only my guess, i'm in Georgian calendar and can't reproduce the issue...

Sorry Abdullah, I missed that file

 

 

@Sergei Baklan's answer may be the key

 

 

so , is there any way to solve the issue ?

Not exactly sure how to resolve it.   You can start by seeing if there's any obvious issues in your system / region settings

 

Region Settings.PNG

@Wyn Hopkins This worked perfectly! Thank-you so much. You saved me hours and tons of frustration!


@Wyn Hopkins wrote:

Hi Abdullah

 

One possible solution is to highlight the column of dates and then select Data > Text to columns > Finish

 

This "tricks" Excel into treating text as numbers

 

Alternatively copy a blank cell then highlight the dates and Paste Special > Add  

 

This again tricks Excel into adding 0 to each cell and therefore the text gets converted to values