Dates Format

%3CLINGO-SUB%20id%3D%22lingo-sub-1618280%22%20slang%3D%22en-US%22%3EDates%20Format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1618280%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%2010%20year%20old%20excel%20sheet%20database.%20Whilst%20working%2C%20the%20dates%20of%20more%20than%201000%20entries%20suddenly%20changed%20from%20DD%2FMM%2FYYYY%20to%20random%20numbers.%20When%20I%20click%20on%20the%20cell%20itself%2C%20the%20right%20date%20appears%20in%20the%20right%20format%20in%20the%20function%20box%20but%20would%20still%20be%20a%20random%20array%20of%20numbers%20in%20the%20actual%20cell.%20What%20has%20happened%3F%26nbsp%3B%3C%2FP%3E%3CP%3EP.S.%20When%20I%20copy%20the%20date%20from%20the%20function%20box%20and%20paste%20it%20into%20the%20cell%20nothing%20changes.%20I've%20tried%20formatting%20the%20cells%20as%20%22Date%22%20and%20nothing%20changes.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1618280%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1618308%22%20slang%3D%22en-US%22%3ERe%3A%20Dates%20Format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1618308%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F774432%22%20target%3D%22_blank%22%3E%40aie_102000%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ecan%20you%20please%20few%20sample%20records%20and%20screenshot%20of%20your%20data%20please%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ERegards%2C%20Faraz%20Shaikh%20%7C%20MCT%2C%20MIE%2C%20MOS%20Master%2C%20Excel%20Expert%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CEM%3E%3CFONT%20color%3D%22%23808080%22%3EIf%20you%20find%20the%20above%20solution%20resolved%20your%20query%20don't%20forget%20mark%20as%26nbsp%3B%3CSPAN%3EOfficial%2FBest%20Answer%26nbsp%3Bto%20help%20the%20other%20members%20find%20it%20more%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FEM%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1618323%22%20slang%3D%22en-US%22%3ERe%3A%20Dates%20Format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1618323%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F156456%22%20target%3D%22_blank%22%3E%40Faraz%20Shaikh%3C%2FA%3E%26nbsp%3BFind%20attached.%20Sorry%20I%20had%20to%20take%20this%20kind%20of%20screenshot%20the%20data%20is%20confidential.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1618327%22%20slang%3D%22en-US%22%3ERe%3A%20Dates%20Format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1618327%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F774432%22%20target%3D%22_blank%22%3E%40aie_102000%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20is%20formatting%20issue%2C%20you%20need%20to%20select%20that%20column%20and%20change%20it%20to%20date%20format.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EShortcut%20to%20switch%20to%20date%20format%20will%20be%20%3CSTRONG%3E%22CTRL%20%2B%20SHIFT%20%2B%203%22%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ERegards%2C%20Faraz%20Shaikh%20%7C%20MCT%2C%20MIE%2C%20MOS%20Master%2C%20Excel%20Expert%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CEM%3E%3CFONT%20color%3D%22%23808080%22%3EIf%20you%20find%20the%20above%20solution%20resolved%20your%20query%20don't%20forget%20mark%20as%26nbsp%3B%3CSPAN%3EOfficial%2FBest%20Answer%26nbsp%3Bto%20help%20the%20other%20members%20find%20it%20more%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FEM%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1618328%22%20slang%3D%22en-US%22%3ERe%3A%20Dates%20Format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1618328%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F156456%22%20target%3D%22_blank%22%3E%40Faraz%20Shaikh%3C%2FA%3E%26nbsp%3BThanks%20for%20the%20response.%20As%20I%20mentioned%20in%20the%20post%2C%20I've%20tried%20doing%20that%20and%20nothing%20changes.%20Also%2C%20when%20I%20use%20the%20CTRL%20Shift%203%20function%20it%20just%20italicizes%20the%20numbers.%20Any%20ideas%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1618333%22%20slang%3D%22en-US%22%3ERe%3A%20Dates%20Format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1618333%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F774432%22%20target%3D%22_blank%22%3E%40aie_102000%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20your%20keyboard%20setting%20are%20change%20to%20some%20other%20region%2C%20check%20your%20keyboard%20setting%20is%20US%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ecan%20you%20try%20changing%20the%20date%20format%20using%20the%20%3CSTRONG%3EHome%20Ribbon%20%26gt%3B%20Short%20Date%3C%2FSTRONG%3E.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20requested%2C%20if%20you%20can%20still%20share%20the%20file%20that%20will%20be%20great%20just%20deleting%20the%20important%20information%20and%20keeping%20just%20issue%20column%20just%20few%20records..%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ERegards%2C%20Faraz%20Shaikh%20%7C%20MCT%2C%20MIE%2C%20MOS%20Master%2C%20Excel%20Expert%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CEM%3E%3CFONT%20color%3D%22%23808080%22%3EIf%20you%20find%20the%20above%20solution%20resolved%20your%20query%20don't%20forget%20mark%20as%26nbsp%3B%3CSPAN%3EOfficial%2FBest%20Answer%26nbsp%3Bto%20help%20the%20other%20members%20find%20it%20more%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FEM%3E%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-1618357%22%20slang%3D%22en-US%22%3ERe%3A%20Dates%20Format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1618357%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F156456%22%20target%3D%22_blank%22%3E%40Faraz%20Shaikh%3C%2FA%3ESwitched%20to%20US%20keyboard%20and%20tried%20CTRL%20SHIFT%203%20and%20nothing%20happened%20(instead%20of%20italicizing%20before).%20Tried%20the%20Home%20Ribbon%20Short%20Date%20nothing%20changes%2C%20it%20literally%20stays%20the%20same%20even%20though%20the%20function%20box%20is%20displaying%20the%20correct%20date%20in%20the%20correct%20format.%3C%2FP%3E%3CP%3EIf%20I%20copy%20it%20onto%20a%20new%20excel%20the%20date%20that%20is%20otherwise%20correct%20in%20the%20function%20box%20becomes%20the%20same%20random%20numbers.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

I have a 10 year old excel sheet database. Whilst working, the dates of more than 1000 entries suddenly changed from DD/MM/YYYY to random numbers. When I click on the cell itself, the right date appears in the right format in the function box but would still be a random array of numbers in the actual cell. What has happened? 

P.S. When I copy the date from the function box and paste it into the cell nothing changes. I've tried formatting the cells as "Date" and nothing changes.

6 Replies
Highlighted

Hi @aie_102000 

 

can you please few sample records and screenshot of your data please 

 

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official/Best Answer to help the other members find it more

Highlighted

@Faraz Shaikh Find attached. Sorry I had to take this kind of screenshot the data is confidential.

Highlighted

Hi @aie_102000,

 

That is formatting issue, you need to select that column and change it to date format.

 

Shortcut to switch to date format will be "CTRL + SHIFT + 3"

 

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official/Best Answer to help the other members find it more

Highlighted

@Faraz Shaikh Thanks for the response. As I mentioned in the post, I've tried doing that and nothing changes. Also, when I use the CTRL Shift 3 function it just italicizes the numbers. Any ideas?

Highlighted

Hi @aie_102000 

 

I think your keyboard setting are change to some other region, check your keyboard setting is US 

 

can you try changing the date format using the Home Ribbon > Short Date

 

As requested, if you can still share the file that will be great just deleting the important information and keeping just issue column just few records..

 

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official/Best Answer to help the other members find it more

 

 

Highlighted

@Faraz ShaikhSwitched to US keyboard and tried CTRL SHIFT 3 and nothing happened (instead of italicizing before). Tried the Home Ribbon Short Date nothing changes, it literally stays the same even though the function box is displaying the correct date in the correct format.

If I copy it onto a new excel the date that is otherwise correct in the function box becomes the same random numbers.