Changing the format of 10,000 dates

%3CLINGO-SUB%20id%3D%22lingo-sub-2733623%22%20slang%3D%22en-US%22%3EChanging%20the%20format%20of%2010%2C000%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2733623%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20I%20need%20help%20changing%20the%20values%20of%20these%20dates%20from%20mm%2Fdd%2Fyyyy%20to%20dd%2Fmm%2Fyyyy.%20I%20have%20already%20tried%20changing%20the%20format%20via%20the%20ctrl%201%20format%20cells%20method.%20This%20seems%20to%20get%20new%20dates%20in%20the%20right%20format%2C%20but%20I%20already%20have%20~10k%20dates%20that%20need%20to%20be%20changed.%20Is%20there%20an%20easy%20way%20to%20do%20this%3F%3C%2FP%3E%3CP%3EThanks%20for%20the%20help%20stranger%20%3CLI-EMOJI%20id%3D%22lia_grinning-face-with-big-eyes%22%20title%3D%22%3Agrinning_face_with_big_eyes%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22jsamodeo_0-1631124193862.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F309050iD477A3332A378D46%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22jsamodeo_0-1631124193862.png%22%20alt%3D%22jsamodeo_0-1631124193862.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2733623%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2733691%22%20slang%3D%22en-US%22%3ERe%3A%20Changing%20the%20format%20of%2010%2C000%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2733691%22%20slang%3D%22en-US%22%3ESelect%20the%20whole%20column%20that%20you%20want%20to%20apply%20the%20format%20and%20then%20CTRL%2B1%20and%20then%20apply%20dd%2Fmm%2Fyyyy%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2733720%22%20slang%3D%22en-US%22%3ERe%3A%20Changing%20the%20format%20of%2010%2C000%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2733720%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F767933%22%20target%3D%22_blank%22%3E%40Juliano-Petrukio%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20that%20changes%20how%20the%20box%20appears%20when%20hovered%20over%2C%20but%20it%20doesn't%20change%20the%20values%20at%20the%20top.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Capture.PNG%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F309058iA8CBC792D611C166%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Capture.PNG%22%20alt%3D%22Capture.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%E2%80%83%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2734757%22%20slang%3D%22en-US%22%3ERe%3A%20Changing%20the%20format%20of%2010%2C000%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2734757%22%20slang%3D%22en-US%22%3EIt%20is%20because%20your%20regional%20settings.%20It%20does%20not%20change%20unless%20you%20change%20your%20computer%20settings.%3CBR%20%2F%3EDont%20worry%2C%20it%20is%20normal.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2783216%22%20slang%3D%22en-US%22%3ERe%3A%20Changing%20the%20format%20of%2010%2C000%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2783216%22%20slang%3D%22en-US%22%3EWhat%20about%20dates%20prior%20to%20the%20year%201900%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2783311%22%20slang%3D%22en-US%22%3ERe%3A%20Changing%20the%20format%20of%2010%2C000%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2783311%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1150225%22%20target%3D%22_blank%22%3E%40jsamodeo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%3EHere%20is%20a%20suggestion%20with%20VBA%20(untested).%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%3EShould%20actually%20work.%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%3EBuild%20a%20button%20into%20the%20sheet%20and%20let%20it%20run%20whenever%20you%20want%20...%20is%20just%20a%20suggestion%20%3A)).%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3ESub%20Datechange()%0ARange(%22C1%3AC10000%22).NumberFormat%20%3D%20%22dd%2Fmm%2Fyyyy%22%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20I%20was%20able%20to%20help%20you%20with%20this%20info.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolinoDE%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWas%20the%20answer%20useful%3F%20Mark%20them%20as%20helpful!%3C%2FP%3E%3CP%3EThis%20will%20help%20all%20forum%20participants.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello, I need help changing the values of these dates from mm/dd/yyyy to dd/mm/yyyy. I have already tried changing the format via the ctrl 1 format cells method. This seems to get new dates in the right format, but I already have ~10k dates that need to be changed. Is there an easy way to do this?

Thanks for the help stranger

jsamodeo_0-1631124193862.png

 

10 Replies
Select the whole column that you want to apply the format and then CTRL+1 and then apply dd/mm/yyyy

@Juliano-Petrukio 

So that changes how the box appears when hovered over, but it doesn't change the values at the top.

Capture.PNG

It is because your regional settings. It does not change unless you change your computer settings.
Dont worry, it is normal.

What about dates prior to the year 1900?

@jsamodeo 

Here is a suggestion with VBA (untested).

Should actually work.

Build a button into the sheet and let it run whenever you want ... is just a suggestion :)).

 

Sub Datechange()
Range("C1:C10000").NumberFormat = "dd/mm/yyyy"
End Sub

 

 

Hope I was able to help you with this info.

 

NikolinoDE

 

Was the answer useful? Mark them as helpful!

This will help all forum participants.

@udayan62 

You just need to set up your excel to accept these dates.

JulianoPetrukio_0-1632513193992.png

 

@NikolinoDE 

As was told before, it doesn't change because the regional settings of the computer.
You can change the view on the spreadsheet but if you click on the formula bar the date will be like was setup on regional settings.

 

JulianoPetrukio_0-1632513740957.jpeg

 

 

Could you please make column C a lot wider so we can actually see the date in the cells in your screenshot

Thanks, that will help us answer
The date format was already solved. The only problem he was not considering is that the regional settings will be presented on formula bar. Well, this is not an issue.
In fact, his doubt was already solved.
Ah I didn’t notice any reply or confirmation for the original poster ?