Unable to Convert Date in Excel
Solved/Closed
awallsy
Posts
3
Registration date
Friday 18 September 2015
Status
Member
Last seen
21 September 2015
-
18 Sep 2015 à 13:59
Ambucias Posts 47312 Registration date Monday 1 February 2010 Status Moderator Last seen 28 March 2026 - 22 Sep 2015 à 16:42
Ambucias Posts 47312 Registration date Monday 1 February 2010 Status Moderator Last seen 28 March 2026 - 22 Sep 2015 à 16:42
Related:
- Cannot change date format in excel
- How to change date format in excel - Guide
- Excel clear formatting - Guide
- Excel mod apk for pc - Download - Spreadsheets
- Arrow keys not working in excel - Guide
- Kingston format utility - Download - Storage
4 responses
Mazzaropi
Posts
1983
Registration date
Monday 16 August 2010
Status
Contributor
Last seen
24 May 2023
147
18 Sep 2015 à 19:23
18 Sep 2015 à 19:23
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
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
Mazzaropi
Posts
1983
Registration date
Monday 16 August 2010
Status
Contributor
Last seen
24 May 2023
147
21 Sep 2015 à 13:49
21 Sep 2015 à 13:49
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
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
Mazzaropi
Posts
1983
Registration date
Monday 16 August 2010
Status
Contributor
Last seen
24 May 2023
147
18 Sep 2015 à 14:54
18 Sep 2015 à 14:54
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.
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
Posts
3
Registration date
Friday 18 September 2015
Status
Member
Last seen
21 September 2015
18 Sep 2015 à 17:34
18 Sep 2015 à 17:34
Hello,
Thank you for the response. It came up as false.
Thank you for the response. It came up as false.
Ambucias
Posts
47312
Registration date
Monday 1 February 2010
Status
Moderator
Last seen
28 March 2026
11,166
22 Sep 2015 à 16:42
22 Sep 2015 à 16:42
Marcilio is a wizard!
21 Sep 2015 à 12:15
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"