200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Hi, I want to know whats the fastest and most efficient way to change date format from 00.00.0000 to dd mmmm yyyy?
I have attached an Excel file here and you'll see the red highlighted that are column B and J are the columns that i wanna change the date format.
Also i want to know what data type that exist in column B and J, is it string or number?
Thanks
Oh yes, this is text that looks like a date, such an annoying little thing but it's easy to change to a date in this case.
First, to figure out what format a cell is, just select it, go to the Home tab an look in the Number box - this cell is formatted as "General" which basically means text. You can also use the function =ISTEXT() to figure out if a cell is formatted as text or not.
To change this text to a date you can use this formula:
=DATE(RIGHT(B2,4),MID(B2,4,2),LEFT(B2,2))This breaks out the pieces of the text that are for the year, month, and day and uses the DATE() function to make it a real date.
Just insert a new column next to B and J and then input the above formula and copy it down for the entire column and then select them and Copy Paste-Special them (Ctrl + C and then Alt + E + S + V and Enter) to change them into the values that the formulas represent. Then you can copy the date values and paste them over the original text that just looks like dates.
This seems like a lot of work but once you get used to it, it will take you no more than 30 seconds to finish the task.