Change date format on import

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,046
Office Version
2010
Platform
Windows
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.
 
Last edited:

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,046
Office Version
2010
Platform
Windows
The following suggestion looks the same as your example; the data is in merged cells.

T202006b.xlsm
BC
5Jan 2, 2021
02-Jan-21
6
3a
Cell Formulas
RangeFormula
C5C5=DATE(RIGHT(B5,4),MONTH(1&LEFT(B5,3)),MID(B5,5,1))
 

namloop

New Member
Joined
Mar 23, 2020
Messages
23
Office Version
365
Platform
Windows
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.
 

Attachments

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,046
Office Version
2010
Platform
Windows
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

New Member
Joined
Mar 23, 2020
Messages
23
Office Version
365
Platform
Windows
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.
 

Attachments

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,046
Office Version
2010
Platform
Windows
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

New Member
Joined
Mar 23, 2020
Messages
23
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Feb 15, 2002
Messages
4,046
Office Version
2010
Platform
Windows
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

New Member
Joined
Mar 23, 2020
Messages
23
Office Version
365
Platform
Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,100,038
Messages
5,472,116
Members
406,804
Latest member
xbinsx

This Week's Hot Topics

Top