Hi Gurus,
I have a sheet with Date Column with different date formats which is in 'General' not date format
Ex: the column contains - 31122024, 1152023,1220224 which is in DDMMYYYY format.
I need the formula to convert all the above Date formats in MMDDYYYY format.
Thanks in advance.
I have attached the sample sheet.
Still using Excel 2010?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
=text(if(len(a1)=7,date(right(a1,4),left(a1,1),mid(a1,2,2)),date(right(a1,4),left(a1,2),mid(a1,3,2))), "mmddyyyy")
Another possibility.
Formula:
Please Login or Register to view this content.
Power Query
Please Login or Register to view this content.
No formulas
Date-->Text to Columns-->Next-->Next-->Date-->DMY-->Finish
Dear Gurus,
Thanks for all your inputs, I really appreciate them. Despite trying all of your formulas and tricks, I am still not getting the correct result.
I tried something as per the attached file.
I want to correct only the row which is hightlighted in 'yellow' or suggest me anyother way, either
i created the formula for zero.
Last edited by jaffirahamed1; 03-04-2024 at 12:53 PM. Reason: Changed the actual attachment
See attached: 31/02/2024 is invalid date!
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
I think I should stress it more clearly, my formula does it all, but you will need to change the year marker inside the sheet from "mm-dd-aaaa" to "mm-dd-yyyy".
Formula:Please Login or Register to view this content.
Dear DJ, I replaced year marker as 'YYYY', but still the same result unfortunately. Check the below image
Please check the post #7, i have attached the sheet, where i have found the formula.
If the dates in column A are REAL dates (not TEXT which is what the format suggests!) then I simply formatted column B as "mm/dd/yyyy"
in B2
=A2
Hi John, Yes, column A are REAL Dates to covert MM/DD/YYYY date format. Thanks.
In your sample there was this indication:
DDMMYYYY
09/02/2025
No mention of 1/1/1900.
Dear DJ, As per in the post #7 attachment, the entire column A is mix of DD/MM/YYYY, D/M/YYYY, DD/M/YYYY, D/MM/YYYY date formats with 'General' category which needs to change to MM/DD/YYYY completely
Last edited by jaffirahamed1; 03-05-2024 at 12:02 AM.
ddmmyyyy isn't the same as dmyyyy of message #10, but its ok, you may not know the difference it makes.
See attached:
Column A: Data >>> Text to Columns >>DMY
Column F
=A2
Custom Format as MM/DD/YYYY
Dear John, There should not manual intervention in the file, its like fully automated, the user just need to update the latest data to see the results immediately. thanks for understanding.
I can only get the conversion to work via Text To Columns THEN transferring data to another column and formatting as MM_DD-YYYY.
Thanks John. Your solution is great. i consider in my other reports. thanks.
Any solution support here, I am waiting for your input? thanks in advance
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks