Changing incorrect Dates to all look the same

aweir2

New Member
Joined
May 5, 2014
Messages
30
I have recieved a large amount of data to scrub, half of the dates were inputted incorrectly. When I go to format cell and change the date format no change is made as excel does not view it as a date.

The way the date is displayed is below. (The zero before the month is what I believe is throwing everything off).

Looking for a quick way to get the date to go from looking like this
20/02/2014 7:00:00

to a date that looks like this
20/2/2014 7:00:00 AM
 

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
It recognizes it as a date for me (my regional settings are UK i.e. dd/mm/yyyy though). Have you tried formatting it as
dd/m/yyyy hh:mm AM/PM
?
 
Upvote 0
My regional settings are English U.S and I have tried formatting it that way and it just doesn't change.
 
Upvote 0
On a copy of your data try...

selecting the column, click data, text to columns, click next twice, select date and DMY then click ok.
Then try the dd/m/yyyy hh:mm AM/PM format.

(and I know it sounds stupid but if it doesn't work do the same but use MDY).
 
Upvote 0
I just gave that a try and still nothing some of the dates change, while others wont do anything at all. Below is an example of what I am working with. The dates without the 0 in front of month change just fine, while the other dates stay as they were.

Also if I manually change them, take out the 0 they still do not respond if I were to change them to a different format.

5/2/14 7:00
6/2/14 7:00
7/2/14 7:00
10/2/14 7:00
11/2/14 7:00
12/2/14 7:00
13/2/2014 7:00
14/2/2014 7:00
17/2/2014 7:00
18/02/2014 7:00:00
19/02/2014 7:00:00
20/02/2014 7:00:00
21/02/2014 7:00:00
24/02/2014 7:00:00
25/02/2014 7:00:00

<tbody>
</tbody>
 
Upvote 0
Afraid your list isn't going to help me find a solution as when I put them in my worksheet I can get the desired result just by formatting the cells.

Can you just check that when you reference one of the offending cells with
PHP:
=ISNUMBER(A1)
changing A1 to suit you get TRUE.

If not can you try the same after following the text to columns routine.
 
Upvote 0
Because my regional settings are different to yours afraid I'm not going to be much help as it seems likes like your Windows regional settings are still overriding Excel which I am a bit surprised at as text to columns normally sorts that.

All I can suggest is in a different column try
PHP:
=TEXT(A1,"dd:m:yyyy hh:mm AM/PM")
but unfortunately if that works it will be text not a real date.
 
Upvote 0
The formula below worked for me (Excel 2007). It gets the numeric value behind the date (e.g., 41761.29167) and then you can format it however you want from there. Formula assumes your data starts in cell A1.

=IF(ISNUMBER(A1),A1,CONCATENATE(LEFT(MID(A1,FIND("/",A1,1)+1,9999),FIND("/",MID(A1,FIND("/",A1,1)+1,9999))-1),"/",LEFT(A1,FIND("/",A1)-1),"/",MID(A1,FIND(" ",A1)-4,4)," 7:00"))*1
 
Upvote 0
It gets the numeric value behind the date (e.g., 41761.29167)

The cells showing FALSE with ISNUMBER don't have a numeric value behind the date. They are Text not a "real" date.

The other thing you could try is
but I suspect you will get a #VALUE error.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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