Home

Excel Date formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-266291%22%20slang%3D%22en-US%22%3EExcel%20Date%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-266291%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20morning%20guys%2C%20I%20have%20a%20grave%20issue.%20In%20excel%20I%20was%20creating%20a%20custom%20date%20format%20in%20the%20type%20%22dd.mm.yyyy%22%20to%20replace%20the%20format%20%22dd%2Fmm%2Fyyyy%22.%20However%20upon%20doing%20so%20only%20two%20cells%20out%20of%20the%20list%20changed%20and%20the%20rest%20remains%20the%20same.%20I%20do%20not%20know%20what%20is%20happening%2C%20I%20checked%20to%20see%20if%20the%20cells%20were%20locked%20but%20they%20weren't.%20They%20are%20just%20not%20changing%20to%20the%20custom%20date%20format%20I%20set.%20Please%20help%20I%20need%20it%20urgently%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-266291%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-267099%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Date%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-267099%22%20slang%3D%22en-US%22%3EThanks%20so%20much%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-266325%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Date%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-266325%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3EThis%20is%20because%20these%20dates%20which%20have%20not%20changed%20are%20not%20real%20dates%2C%20they%20are%20in%20text%20format!%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3EYou%20have%20to%20convert%20them%20to%20real%20dates%20before%20you%20apply%20the%20custom%20date%20format!%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3ETo%20do%20this%20quickly%2C%20please%20try%20this%20trick%3A%3C%2FSPAN%3E%3C%2FP%3E%3CUL%3E%3CLI%3EHighlight%20them%20all%3C%2FLI%3E%3CLI%3EGo%20to%20Data%20%26gt%3B%26gt%3B%20Data%20Tools%20%26gt%3B%26gt%3B%20Text%20to%20Columns%3C%2FLI%3E%3CLI%3EIn%20Text%20to%20Columns%20window%2C%20click%20Next%20twice%20to%20reach%20to%20step%203%20of%203%3CSTRONG%3E%3CBR%20%2F%3E%3C%2FSTRONG%3E%3C%2FLI%3E%3CLI%3ESelect%20Date%2C%20and%20choose%20the%20format%20of%20(DMY)%20as%20shown%20in%20the%20below%20screenshot%3C%2FLI%3E%3CLI%3EHit%20Finish%3C%2FLI%3E%3C%2FUL%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%20839px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F55391i6C3593E65D66F28B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Text%20to%20Columns%20-%20Date%20DMY.png%22%20title%3D%22Text%20to%20Columns%20-%20Date%20DMY.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E
aldayne henry
New Contributor

Good morning guys, I have a grave issue. In excel I was creating a custom date format in the type "dd.mm.yyyy" to replace the format "dd/mm/yyyy". However upon doing so only two cells out of the list changed and the rest remains the same. I do not know what is happening, I checked to see if the cells were locked but they weren't. They are just not changing to the custom date format I set. Please help I need it urgently

2 Replies
Highlighted

Hi,

 

This is because these dates which have not changed are not real dates, they are in text format!

You have to convert them to real dates before you apply the custom date format!

 

To do this quickly, please try this trick:

  • Highlight them all
  • Go to Data >> Data Tools >> Text to Columns
  • In Text to Columns window, click Next twice to reach to step 3 of 3
  • Select Date, and choose the format of (DMY) as shown in the below screenshot
  • Hit Finish

 

Text to Columns - Date DMY.png

 

Hope that helps

Highlighted
Thanks so much
Related Conversations
Link Sheet 2 to Sheet 1 but 'only' if........
WhopRBopr1 in Excel on
2 Replies
Database Structure
leuki in Excel on
0 Replies
Unable to delete empty rows and columns
G_Torres in Excel on
4 Replies
Help with simple formulas please
nursekimberley in Excel on
3 Replies
Excel or Access? basic advice from which to start from.
grifton in Excel on
2 Replies