Changing incorrect Dates to all look the same

[aweir2]

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

 

[aweir2]

Hmm...Can you paste a few rows of your data where this is happening. The formula is supposed to account for this.

In Time	In Time Formatted
10/4/14 23:00	10/4/14 23:00
1/1/14 7:00	1/1/14 7:00
6/1/14 7:00	6/1/14 7:00
10/1/14 6:45	10/1/14 6:45
13/1/2014 7:00:00 AM	1/13/14 7:00
29/1/2014 7:00:00 AM	1/29/14 7:00
4/2/14 6:36	4/2/14 6:36
5/2/14 7:00	5/2/14 7:00
6/2/14 7:00	6/2/14 7:00
7/2/14 7:00	7/2/14 7:00
10/2/14 7:00	10/2/14 7:00
11/2/14 7:00	11/2/14 7:00
12/2/14 7:00	12/2/14 7:00
13/2/2014 7:00	2/13/14 7:00
14/2/2014 7:00	2/14/14 7:00
17/2/2014 7:00	2/17/14 7:00
18/02/2014 7:00:00	2/18/14 7:00
19/02/2014 7:00:00	2/19/14 7:00
20/02/2014 7:00:00	2/20/14 7:00
21/02/2014 7:00:00	2/21/14 7:00
24/02/2014 7:00:00	2/24/14 7:00

<colgroup><col><col></colgroup><tbody>
</tbody>

As you can see I think because the formula is are putting the month first for the non numeric valued dates, it is having the numeric valued dates read the month first even though there isn't a swap being made. I have italicized the non-numeric value dates.

 

[MARK858]

Are you 100% certain that your windows regional settings (via control panel) aren't set as English(UK) as getting 1/6/yyyy to read as 1st June is how our dates are in the UK.

 

[aweir2]

Are you 100% certain that your windows regional settings (via control panel) aren't set as English(UK) as getting 1/6/yyyy to read as 1st June is how our dates are in the UK.

I just check and they are set to the proper Time Zone for here in Canada

 

[MARK858]

Not the time zone the regional date format.
What does your short date say?

 

[aweir2]

Not the time zone the regional date format.
What does your short date say?

Format is in English (United States)
Short date M/D/Y

 

[MARK858]

In a separate sheet type in manually the table below and format the cells as number. In the same order what do you get in the cells?

	A​
1​	2/17/14 7:00​
2​	2/18/14 7:00​
3​	2/19/14 7:00​
4​	17/2/14 7:00​
5​	18/2/14 7:00​
6​	19/2/14 7:00​

 

[aweir2]

In a separate sheet type in manually the table below and format the cells as number. In the same order what do you get in the cells?

	A​
1​	2/17/14 7:00​
2​	2/18/14 7:00​
3​	2/19/14 7:00​
4​	17/2/14 7:00​
5​	18/2/14 7:00​
6​	19/2/14 7:00​

<tbody>
</tbody>

41687.29

41688.29

41689.29

17/2/14 7:00

18/2/14 7:00

19/2/14 7:00

<colgroup><col></colgroup><tbody>
</tbody>

This is what I got when I formatted into a number, my computer obviously wants to read the month first, which is fine. Is there a way to get the numeric valued dates to flip the day and month, so it would read properly.

 

[MARK858]

Try

Excel Workbook
	A	B
1	10/4/2014 23:00	10/4/2014 23:00
2	1/1/2014 7:00	1/1/2014 7:00
3	6/1/2014 7:00	6/1/2014 7:00
4	10/1/2014 6:45	10/1/2014 6:45
5	13/1/2014 7:00:00 AM	1/13/2014 7:00
6	29/1/2014 7:00:00 AM	1/29/2014 7:00
7	4/2/2014 6:36	4/2/2014 6:36
8	5/2/2014 7:00	5/2/2014 7:00
9	6/2/2014 7:00	6/2/2014 7:00
10	7/2/2014 7:00	7/2/2014 7:00
11	10/2/2014 7:00	10/2/2014 7:00
12	11/2/2014 7:00	11/2/2014 7:00
13	12/2/2014 7:00	12/2/2014 7:00
14	13/2/2014 7:00	2/13/2014 7:00
15	14/2/2014 7:00	2/14/2014 7:00
16	17/2/2014 7:00	2/17/2014 7:00
17	18/02/2014 7:00:00	2/18/2014 7:00
18	19/02/2014 7:00:00	2/19/2014 7:00
19	20/02/2014 7:00:00	2/20/2014 7:00
20	21/02/2014 7:00:00	2/21/2014 7:00
21	24/02/2014 7:00:00	2/24/2014 7:00
Sheet3

 

[MARK858]

Or possibly going by post #19

Excel Workbook
	A	B
1	10/4/2014	April 10, 2014
2	1/1/2014	January 01, 2014
3	6/1/2014	January 06, 2014
4	10/1/2014	January 10, 2014
5	13/1/2014 7:00:00 AM	January 13, 2014
6	29/1/2014 7:00:00 AM	January 29, 2014
7	4/2/2014	February 04, 2014
8	5/2/2014	February 05, 2014
9	6/2/2014	February 06, 2014
10	7/2/2014	February 07, 2014
11	10/2/2014	February 10, 2014
12	11/2/2014	February 11, 2014
13	12/2/2014	February 12, 2014
14	13/2/2014 7:00	February 13, 2014
15	14/2/2014 7:00	February 14, 2014
16	17/2/2014 7:00	February 17, 2014
17	18/02/2014 7:00:00	February 18, 2014
18	19/02/2014 7:00:00	February 19, 2014
19	20/02/2014 7:00:00	February 20, 2014
20	21/02/2014 7:00:00	February 21, 2014
21	24/02/2014 7:00:00	February 24, 2014
22	3/1/2014	January 03, 2014
23	6/1/2014	January 06, 2014
24	13/12/2014 7:30	December 13, 2014
Sheet3

Although I think you will have issues with it....

 

[aweir2]

The formula is flipping the non-numeric values to read month/day/year which if great because it will show 2/12/2014 (Feb 12th 2014. However for the dates with numeric values it is reading month/day/year however it shows 12/2/2014, there for it is reading Dec 2nd,2014 (Which obviously hasn't even happened yet) is there a way to change the formula so it switches both non-numeric value and numeric value dates for the month to show first followed by the date. If you take a look at the set of Data both are set up as Day/Month/Year.