[SIMS] or [Excel] Date convert Text to Short Date

[timbo343]

Is there a way to convert a text date 01 January 1970 to a short date 01/01/70 in excel that has been exported via a report in SIMS.

 

Thanks

[ChrisH]

I use this macro in my reports, I cant remember why I do the number format twice but there is probably a reason. Just trying to do it in Excel never worked for me. You will need to change the cell range or change the code to work on the selected range.

 

Sub FixDates()
Dim lastRow, lastColumn As Integer
Dim rnData As Range
With Sheets("Sheet1")
'Remove the teachers first names
lastRow = .UsedRange.Rows.Count
LastCol = .UsedRange.Columns.Count

'Miss the titles else error
Set rnData = .Range(.Cells(2, 13), .Cells(lastRow, 13))

For Each xCell In rnData
   If xCell.value <> vbNullString Then
   xCell.NumberFormat = "dd/mm/yyyy"
   xCell.value = DateValue(xCell.value)
   xCell.NumberFormat = "dd/mm/yyyy"
   End If
Next xCell

End With
End Sub

[MrMat]

If you don't want to use VBA, you can instead use 'Text to Columns'.

 

Highlight the column, click the Data tab, select Text to Columns, select DELIMITED and then hit finish.

 

That will change the format of the dates into actual dates, which you can then format in any way you like.

Edited July 19, 2016 by MrMat

[timbo343]

If you don't want to use VBA, you can instead use 'Text to Columns'.

 

Highlight the column, click the Data tab, select Text to Columns, select fixed width and then hit finish.

 

That will change the format of the dates into actual dates, which you can then format in any way you like.

 

Thank you. The text to columns is just like importing a text file . Thank you again.

[howartp]

If you don't want to use VBA, you can instead use 'Text to Columns'.

Ooh, didn't know that!

[MrMat]

Edited my post as i gave bad information!!

 

Select delimited rather than Fixed length. Sorry about that.

[sparkeh]

Slightly late to the party but just doing this myself. I found easiest way is to use the datevalue function that turns the text into a date in serial format, if you format the cell as a date you can display it however you want