Change date format on import

[namloop]

Good morning

I am importing some data and I am having two different issues in col. B.
In rows 3 this data says Nov-9 (the 9th of November) when I try to change the format it will say November 2009.
Is there any manipulation that can be done to correct this format?

Second issue
In lines 12/13 is a combination of the date January 2, 2021 - I have tried to Concatenate the two but the data then is not seen as a date - is there a better way? - ultimately I need it to read 02-Jan-21.

Many thanks for your assistance in advance.
Stephen

 

[Dave Patton]

Do you now have the correct dates for your very first question in post "#1.

What information is in the cell imported?
What result do you require?

If you require a formula to convert, please provide several examples and the expected results.

 

[namloop]

Dave - thank you for your perseverance, I am learning more about Excel through people like you!
This has now remedied my issue.
Best regards
Stephen

 

[namloop]

The check for extraneous characters would have shown which cell has the problem.
Excel's Formula Evaluation showed the contents of B13 had a space at the end "2013 ".

=DATE(LEFT(B13,4),MONTH(LEFT(B12,3)&1),MID(B12,5,1)) also works.

Unless you need the cell to be Text, do not convert to text; just format or custom format to your preference.

There is an extraneous character in cell B13.
Try =Len(B13) the result is 5!!

Try this formula

=DATE(LEFT(B13,4),MONTH(1&LEFT(B12,3)),MID(B12,5,1))

Then format the cell to your preference.

If all of the dates are 2021, the following would work
=DATE(2021,MONTH(1&LEFT(B12,3)),MID(B12,5,1))

Dave both of these worked. The only problem now is the samples I used were (days of the month ) 1-9. When the date is the 13th of Month there is an extra character and it throws it out.
I have attached a sample file here

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Many thanks
Stephen

 

[Dave Patton]

=DATE(LEFT(C17,4),MONTH(LEFT(C16,3)&1),MID(C16,5,LEN(C16)-5))

 

[namloop]

=DATE(LEFT(C17,4),MONTH(LEFT(C16,3)&1),MID(C16,5,LEN(C16)-5))

BRILLIANT! I have just trialed and it has now resolved!
Many thanks again
Stephen

 

[Dave Patton]

You could use Find and Replace to remove the extra character in the sheet.
The formula is then a little simpler.
I also included an alternative.

Test Sheet July 1.xlsx
	C	D	E
2	Feb 24,	24-Jan-21	24-Feb-21
3	2021
Sheet1

Cell Formulas
Range	Formula
D2	D2	=DATE(C3,MONTH(LEFT(C3&1)),MID(C2,5,LEN(C2)-5))
E2	E2	=--(MID(SUBSTITUTE(C2,",",""),5,2)&LEFT(SUBSTITUTE(C2,",",""),3)&C3)

 

[Dave Patton]

or clean up the data and then use a formula

Test Sheet July 1.xlsx
	C	D		F
1
2	Feb 24,	Feb 24		24-Feb-21
3	2021	2021
4	Feb 24,	Feb 24		24-Feb-21
5	2021	2021
6	Feb 28,	Feb 28		28-Feb-21
7	2021	2021
8	Feb 28,	Feb 28		28-Feb-21
9	2021	2021
10	Mar 4,	Mar 4		04-Mar-21
11	2021	2021
Sheet1

Cell Formulas
Range	Formula
F2,F10,F8,F6,F4	F2	=--(MID(D2,5,2)&LEFT(D2,3)&D3)
D2:D11	D2	=LEFT(C2,LEN(C2)-1)

 

[Dave Patton]

T202006b.xlsm
	B	C	D	E
18	Feb 24,	Feb 24	24-Feb-21	24-Feb-21
19	2021	2021
20	Feb 24,	Feb 24	24-Feb-21	24-Feb-21
21	2021	2021
22	Feb 28,	Feb 28	28-Feb-21	28-Feb-21
23	2021	2021
24	Feb 28,	Feb 28	28-Feb-21	28-Feb-21
25	2021	2021
26	Mar 4,	Mar 4	04-Mar-21	04-Mar-21
27	2021	2021
28
3a

Cell Formulas
Range	Formula
D18,D26,D24,D22,D20	D18	=--(MID(C18,5,2)&LEFT(C18,3)&C19)
E18,E26,E24,E22,E20	E18	=DATE(C19,MONTH(C18),RIGHT(C18,2))
C18:C27	C18	=LEFT(B18,LEN(B18)-1)