Date reformatting help

ryuwrx

New Member
Joined
Sep 15, 2009
Messages
4
Hello all,

It's been a while since I was last here, but I have a new question that I'm hoping the gurus can help me with. I'm working on an excel doc that has 25k lines of data. One of the columns has a date currently formatted to "mm/dd" and I need to get the format to show "mm/dd/yyyy". If I change the column formatting to the proper formatting, excel doesn't recognize that "mm/dd" is a proper date, it's only seeing it as "nn/nn". Is there a simple solution to adding in the four digit year and the slash mark, so that excel recognizes it as a proper date format? I'm not too concerned about what year it adds because I can use the find/replace to change the years since they are sequential. Any help is appreciated.

Ryan
 
Last edited:

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,416
If you have mm/dd in column A ...
  • Select column A
  • Select from the menu Data\ Text to Columns
    1. Select Delimited and next
    2. De-select all the checkboxes and then next
    3. Select "Date" in the Column data format
    4. Finish

Then you should be able to format column A with any date format you like.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,325
Messages
5,658,171
Members
418,430
Latest member
Chlwls808

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top