I am currently collecting the birthdays of all users to enter into a single excel file the users have filled their DOB in DD/MM/YYYY order as shown in the image 1 below on excel online when I open the excel the number formats are switched to MM/DD/YYYY for some of the dates other dates which are above 12 are staying the same the second picture shows the change I have tried text to column feature still no help and tried and fiddled around with date formats still no use need some help ....
any way to fix it ok if this can be fixed by excel commands or anyway else there are more than 10k data so manual editing is out the question
CodePudding user response：
It seems your users have entered their dates as DD/MM/YYYY, but Excel online expected MM/DD/YYYY. Hence dates where D>12 were transformed to a text string; and where D<=12 were interpreted as MDY.
Formula changed as dates were entered DMY and not DDMMYYYY
Best would be for users to enter their dates using a userform or date picker where you have control over what is entered.
You can try the following to transform the dates into "real dates":
=IF(ISNUMBER(F2),DATE(YEAR(F2),DAY(F2),MONTH(F2)),
LET(x,SUBSTITUTE(F2,"/",REPT(" ",99)),dmy,--TRIM(MID(x,{1,99,198},99)), DATE(INDEX(dmy,3),INDEX(dmy,2),INDEX(dmy,1))))
and fill down