Change date format on import

Dave Patton

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

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

namloop

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

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


Many thanks
Stephen
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,046
Office Version
2010
Platform
Windows
=DATE(LEFT(C17,4),MONTH(LEFT(C16,3)&1),MID(C16,5,LEN(C16)-5))
 

namloop

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

Well-known Member
Joined
Feb 15, 2002
Messages
4,046
Office Version
2010
Platform
Windows
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
CDE
2Feb 24,
24-Jan-21
24-Feb-21
32021
Sheet1
Cell Formulas
RangeFormula
D2D2=DATE(C3,MONTH(LEFT(C3&1)),MID(C2,5,LEN(C2)-5))
E2E2=--(MID(SUBSTITUTE(C2,",",""),5,2)&LEFT(SUBSTITUTE(C2,",",""),3)&C3)
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,046
Office Version
2010
Platform
Windows

Watch MrExcel Video

Forum statistics

Threads
1,100,037
Messages
5,472,112
Members
406,804
Latest member
xbinsx

This Week's Hot Topics

Top