Converting Dates

brettvba

MrExcel MVP
Joined
Feb 18, 2002
Messages
1,030
I have a few hundred dates
reading 1020312 etc
which I want to convert to a date either
120302 or tuesday 12 march 2002
can this be done?
 

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.
One way:

With the data in A1,

=DATE(LEFT(A1,3)+1900,MID(A1,4,2),RIGHT(A1,2))

and format the cell as the date format you want.

Jay

EDIT: If you are working with dates prior to 2000 and the first 2 digits (rather than 3) are 99 for example, use the more robust, but longer formula:

=DATE(LEFT(A1,LEN(A1)-4)+1900,MID(A1,LEN(A1)-3,2),RIGHT(A1,2))
This message was edited by Jay Petrulis on 2002-04-15 14:30
 
Upvote 0
Try this it'll change those numbers to dates wether it relevent dates i dunno gut you could merge the last formulea with this

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 4/15/2002 by Jamie Wood
'

'

Range("B18:B22").Select
Selection.NumberFormat = "yy/dd/mm"
End Sub
 
Upvote 0
Jay nothing seems to show up in the cell when you put the formula in there but when you hover over the top of the cell it tells you the correct date??
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top