Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel Date Conversion Formula 3

Status
Not open for further replies.
RonCT

RonCT

Technical User
Dec 11, 2003
37
0
0
US
Can someone tell me the formula to convert this date 20040331 to 03/31/2004? Thanks!
 
Sort by date Sort by votes
Assuming that all dates contain 8 characters,
Code: 
=(LEFT(RIGHT(A1,4),2) & "/" & RIGHT(A1,2) & "/" & LEFT(A1,4))*1
Then format the cell any way you wish, E.g. Format>Cells then choose Custom and type in MM/DD/YYYY.

Hope that helps!
John
 
Upvote 0 Downvote
Not sure if you are looking for a VB solution but if not:

Use Text to columns using fixed

Then rebuild using concatenate and include a cell with / in it


Regards

Keith
 
Upvote 0 Downvote
The above will work but I'll also provide a way to put it in just "date" format.

Assuming the data is in A1...

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

arvarr
  • Locked
  • Question
Excel Formula Help 1
Replies
7
Views
153
SkipVought
S
TomSalvato
  • Locked
  • Question
EXCEL-formula help 2
Replies
6
Views
102
TomSalvato
TomSalvato
Bijugate
  • Question
Excel update question
Replies
0
Views
106
Bijugate
Bijugate
Pirouette
  • Locked
  • Question
Excel workbook
Replies
1
Views
133
combo
combo
techie131
  • Locked
  • Question
Excel Date format
2
Replies
20
Views
221
SkipVought
S

Part and Inventory Search

Sponsor

Back
Top