Dates - Not being treated as dates

Welsh Mark3

Board Regular
Joined
Apr 7, 2014
Messages
164
Office Version
  1. 365
I have a range of dates that are not being treated as dates. I have used the format cells function and changed to UK format (DD/MM/YYYY). I have also used text to columns function.

Does anyone have any recommendations on how I can convert to dates? I have included some examples below. I can't figure why these wouldn't be treated as dates


DATE_APPOINTED
14/03/2012
18/12/2018
19/08/2019
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
The data you have entered above copies and pastes fine into Excel and is treated as a date, so whatever is wrong in your spreadsheet isn't replicated here.
When I export certain values / dates from databases, certain fields are not treated correctly (Numbers are treated as text, dates treated as text. I have a generic macro I run to fix all of those issues but I use it a lot, you could just do it as a one off.

Highlight your whole column of dates,
Press Ctrl + H (Find and Replace)
Replace a Space with nothing (So just hit the space bar once in the find field, and make sure the replace field is empty)
Replace a / with a / (This looks like it does nothing but it forces Excel to recalculate all affected cells, thus changing them to dates).

Try that.
 
Upvote 0
Solution
The data you have entered above copies and pastes fine into Excel and is treated as a date, so whatever is wrong in your spreadsheet isn't replicated here.
When I export certain values / dates from databases, certain fields are not treated correctly (Numbers are treated as text, dates treated as text. I have a generic macro I run to fix all of those issues but I use it a lot, you could just do it as a one off.

Highlight your whole column of dates,
Press Ctrl + H (Find and Replace)
Replace a Space with nothing (So just hit the space bar once in the find field, and make sure the replace field is empty)
Replace a / with a / (This looks like it does nothing but it forces Excel to recalculate all affected cells, thus changing them to dates).

Try that.
Thank you
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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
Back
Top