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]

Try using Excel's Formula Evaluate and identify the part of the formula that is not working.
You may have extraneous spaces in the text fields.
Ensure that you Do Not have merged cells.

Copy my suggestion on a new sheet and confirm that the formula works on your system.
Use the icon below the F(x) and paste the example to your new sheet.

 

[Dave Patton]

The following suggestion looks the same as your example; the data is in merged cells.

T202006b.xlsm
	B	C
5	Jan 2, 2021	02-Jan-21
6
3a

Cell Formulas
Range	Formula
C5	C5	=DATE(RIGHT(B5,4),MONTH(1&LEFT(B5,3)),MID(B5,5,1))

 

[namloop]

Try using Excel's Formula Evaluate and identify the part of the formula that is not working.
You may have extraneous spaces in the text fields.
Ensure that you Do Not have merged cells.

Copy my suggestion on a new sheet and confirm that the formula works on your system.
Use the icon below the F(x) and paste the example to your new sheet.

 

[Dave Patton]

Did you try the suggestions?
Your picture does not help. You can post an extract of your sheet with XL2BB. See XLsBB at the end of the icons shown just above this message.

 

[namloop]

Hi Dave - I do not have merged cells
I used the EVALUATE command - I have not see this before - and copied the results from each of the steps on the attached.

 

[namloop]

Just trying to install XL2BB now

 

[Dave Patton]

I have never seen that message myself.

Try deleting the formula.
Ensure the cell is General format.
Type in the formula and hopefully it will work.

Did my suggestion work on a new sheet?

 

[namloop]

No - same result - colB is general as is C until the string runs then it is DATE, with #VALUE! message
Am stepping through installing XLB2BB whilst at work answering phones - sorry for fragmented replies - appreciate the help though.
Stephen

 

[Dave Patton]

Try entering the formula in a unused part of your sheet; possibly P12.
You cannot enter the formula over a text area.
Did you try any of the suggestions on a fresh clean sheet or workbook????

I do not know what you mean with
" colB is general as is C until the string runs then it is DATE "

 

[namloop]

Dave
Thanks for your patience.
I have re entered into a new sheet and ran that =TEXT string elsewhere - same result.

I do not know what you mean with
" colB is general as is C until the string runs then it is DATE "

I meant to say - the column I entered the Jan2, and 2021 into was GENERAL FORMAT as was the =TEXT string - when the text string is entered the format of that column changes to DATE

I note in this string =DATE(RIGHT(B5,4),MONTH(1&LEFT(B5,3)),MID(B5,5,1)) there is no refernce to the data in cell B6 which holds the calendar year?

Many thanks
Stephen