Unable to Convert Date in Excel [Solved/Closed]

awallsy 3 Posts Friday September 18, 2015Registration date September 21, 2015 Last seen - Sep 18, 2015 at 01:59 PM - Latest reply: Ambucias 55024 Posts Monday February 1, 2010Registration date September 21, 2018 Last seen
- Sep 22, 2015 at 04:42 PM
I am trying to change all of the dates in an excel spreadsheet to the mm/dd/yyyy format, but when I go to format the cells, it does not change, even when I have tried a custom format. In the format cells window, it says that it is currently set at *3/14/2001, but it is appearing as Apr 1 2013, Apr 3 2013 ...etc. Is there any way to format all of the cells the way that I want them? I have 1000+ dates, so I really do not have the time to go in and manually change all of them.
See more 

7 replies

Mazzaropi 1834 Posts Monday August 16, 2010Registration dateContributorStatus May 30, 2018 Last seen - Sep 18, 2015 at 02:54 PM
0
Thank you
awallsy, Good afternoon.

The source of the problem can be varied.

These dates were imported from another application?

These dates may be as text to Excel, so he can not change format other than by formula.

Take a test:
Select a cell containing one of these dates;
Somewhere use the formula: = ISNUMBER (CELL WITH DATE).

If the answer is TRUE, simply means that the field is really a date.
If it is FALSE means that Excel is considering this date as TEXT.

Please let us know the result of the test so we can advise you effectively.
awallsy 3 Posts Friday September 18, 2015Registration date September 21, 2015 Last seen - Sep 18, 2015 at 05:34 PM
Hello,

Thank you for the response. It came up as false.
Mazzaropi 1834 Posts Monday August 16, 2010Registration dateContributorStatus May 30, 2018 Last seen - Sep 18, 2015 at 07:23 PM
0
Thank you
awallsy, Good evening.

If your test came as FALSE then ALL your dates are TEXT.
If all dates follow the same pattern that you mentioned, so I'm able to help you to fix it.

Suppose your date are at column A.

Date at A2 = Apr 1 2013

B2 --> Formula
=DATE(RIGHT(A2,4),VLOOKUP(LEFT(A2,3),$E$2:$F$13,2,FALSE);MID(A2,5,LEN(A2)-9))

Month Table
.....E......F
2...Jan...1
3...Feb...2
4...Mar...3
5...Apr...4
6...Mai...5
7...Jun...6
8...Jul....7
9...Aug...8
10..Sep..9
11..Oct..10
12..Nov..11
13..Dec..12

B2 --> Result --> 01/04/2013

I did an example with a formula implemented to help you:
http://speedy.sh/9Q5Cf/18-09-2015-EN-Kioskea-Date-Convert-TEXT-to-REAL-DATE-OK.xlsx
Now that the date are real date you can change a format using a cell menu format. Try it.

IF you prefer there is another formula.
No table.

B2 --> =DATE(RIGHT(A2,4),ROUNDUP(FIND(LEFT(A2,3),"janfebmaraprmaijunjulaugsepoctnovdec")/3,0),MID(A2,5,LEN(A2)-9))

I prefer the first method.
Is more profissional and elegant way.

Is that what you're looking for?
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
awallsy 3 Posts Friday September 18, 2015Registration date September 21, 2015 Last seen - Sep 21, 2015 at 12:15 PM
Hello again,

I tried both formulas and neither worked, and I think I know why - I just realized that the dates are also showing a time after them as well. For example "Apr 3 2013 4:32PM"
Mazzaropi 1834 Posts Monday August 16, 2010Registration dateContributorStatus May 30, 2018 Last seen - Sep 21, 2015 at 01:49 PM
0
Thank you
awallsy, Good afternoon.

Try to use:

B2 -->
=DATE(MID(A2,FIND(":",A2)-6,4), VLOOKUP(LEFT(A2,3), $E$2:$F$13,2,FALSE), MID(A2,5,LEN(A2)-16))

Is that what you're looking for?
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
Yes, that worked! Thank you very much for your help!
Ambucias 55024 Posts Monday February 1, 2010Registration date September 21, 2018 Last seen - Sep 22, 2015 at 04:42 PM
0
Thank you
Marcilio is a wizard!