Dates - Not being treated as dates

Welsh Mark3

Board Regular
Joined
Apr 7, 2014
Messages
155
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

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Little_Clubber

New Member
Joined
Nov 10, 2017
Messages
27
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.
 
Solution

Welsh Mark3

Board Regular
Joined
Apr 7, 2014
Messages
155
Office Version
  1. 365
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
 

Forum statistics

Threads
1,176,634
Messages
5,904,156
Members
435,074
Latest member
McKay_S

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