Converting mm/dd/yyyy to yyyy/mm/dd

[joker_frowns]

Hello,

I have a spreadsheet of weekly reported data going back 3 years.
Currently the dates are reported in mm/dd/yyyy format and I want to be
able to sort the activities chronologically. Is there any way I can
apply a yyyy/mm/dd mask to my date column so that the SORT function
will see these in the correct order?

I am using Excel 2003 and the only date formats it gives me the option
to select have the year at the end of the entry (therefor it is
sorting by year or month and not doing a proper chronological sort).

Many thanks,

Ryan

 

[Ron Rosenfeld]

Hello,

I have a spreadsheet of weekly reported data going back 3 years.
Currently the dates are reported in mm/dd/yyyy format and I want to be
able to sort the activities chronologically. Is there any way I can
apply a yyyy/mm/dd mask to my date column so that the SORT function
will see these in the correct order?

I am using Excel 2003 and the only date formats it gives me the option
to select have the year at the end of the entry (therefor it is
sorting by year or month and not doing a proper chronological sort).

Many thanks,

Ryan

Give an example of how your dates are sorting, and how you wish them to sort.

It sounds as if your dates may be stored as text, and not as "Excel Dates". It
may even be the case that some cells are text, and others are "Excel Dates".

Excel stores dates as sequential numbers, with 1/1/1900 = 1. The values should
sort chronologically (numerically) regardless of how they are formatted.

So the first thing is to determine what you have in your cells.

In addition to the above, please post the result of the function
=ISNUMBER(cell_ref) where cell_ref contains one of these mis-sorted dates. Do
so for several dates at the beginning and end of some month.

Also, your Windows REgional settings (Start/Control Panel/Regional and Language
Options/Customize/Date Short Date Format
--ron

 

[David Biddulph]

If you've got real dates, as distinct from text, then (regardless of the
display format) excel will sort chronologically. [And if the formats you
can see don't suit you, use an appropriate custom format.]

If you've got text, formatting the cells will have no effect. In this case,
you may be able to use Data/ text to columns ... to translate the text to
real dates.

 

[joker_frowns]

You're absolutely right, on further investigation they are just text
strings, I'm not sure how I overlooked it. The dates are all text
based ranges.

12/19/05 - 12/25/05
12/26/05 - 1/1/06
12/26/05 - 1/1/06
1/2/06 - 1/8/06
1/2/06 - 1/8/06
2/5/07 - 2/11/07
2/5/07 - 2/11/07
2/12/07 - 2/18/07
2/12/07 - 2/18/07
2/19/07 - 2/25/07
2/19/07 - 2/25/07
2/26/07 - 3/4/07
2/26/07 - 3/4/07
3/5/07 - 3/11/07
3/5/07 - 3/11/07
3/12/07 - 3/18/07
3/12/07 - 3/18/07
3/19/07 - 3/25/07


Is there any macro out there that could reformat all these dates to
move the year to the front of the dates? ... this is column A of an
A:M spreadsheet and I would like to be able to sort by other things
than Date, and then go back to resort by date chronologically after.

Thanks,

Ryan

 

[David Biddulph]

You may find it cleaner to split the data from each cell to give two columns
of real dates.
Data/ Text to columns/ Delimited/ Space and add a hyphen as "Other". For
each column select date and MDY.
If you wish, you can select a destination other than the start of the source
data, and thus leave the source data alongside the two columns of real
dates. Select the whiole range and sort by the appropriate column(s).

 

[Rick Rothstein \(MVP - VB\)]

I'm thinking you would find your worksheet more flexible if you put each
part of those date ranges in their own column rather than list them as you
have (each column could contain real dates that way). However, assuming a
redesign of your spreadsheet is not something you could easily do, give the
following two macros a try. The first (AddSortableDate) will place sortable
text in front of each cell in Column A so that you can perform your sort and
the second (RemoveSortableDate), to be run after the sort is complete, will
restore Column A to how it currently looks. Note: Change the worksheet name
reference in the With statement to the actual name of your worksheet and
change the number assigned to the DataStartRow constant to the row number of
the first date range in Column A (I assumed it would be 2, figuring Row 1
was a header row).

Sub AddSortableDate()
Dim X As Long
Dim LastRow As Long
Dim LeftDate As Date
Dim CellValue As String
Dim Parts() As String
Const DataStartRow = 2
With Worksheets("Sheet2")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For X = DataStartRow To LastRow
CellValue = .Cells(X, "A").Value
Parts = Split(CellValue, "/")
LeftDate = DateSerial(Left(Parts(2), 2), Parts(0), Parts(1))
.Cells(X, "A").Value = Format$(LeftDate, "yyyy-mm-dd ") & CellValue
Next
End With
End Sub

Sub RemoveSortableDate()
Dim X As Long
Dim LastRow As Long
Dim LeftDate As Date
Const DataStartRow = 1
With Worksheets("Sheet2")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For X = DataStartRow To LastRow
.Cells(X, "A").Value = Mid$(.Cells(X, "A").Value, 12)
Next
End With
End Sub

Rick

 

[Ron Rosenfeld]

You're absolutely right, on further investigation they are just text
strings, I'm not sure how I overlooked it. The dates are all text
based ranges.

12/19/05 - 12/25/05
12/26/05 - 1/1/06
12/26/05 - 1/1/06
1/2/06 - 1/8/06
1/2/06 - 1/8/06
2/5/07 - 2/11/07
2/5/07 - 2/11/07
2/12/07 - 2/18/07
2/12/07 - 2/18/07
2/19/07 - 2/25/07
2/19/07 - 2/25/07
2/26/07 - 3/4/07
2/26/07 - 3/4/07
3/5/07 - 3/11/07
3/5/07 - 3/11/07
3/12/07 - 3/18/07
3/12/07 - 3/18/07
3/19/07 - 3/25/07


Is there any macro out there that could reformat all these dates to
move the year to the front of the dates? ... this is column A of an
A:M spreadsheet and I would like to be able to sort by other things
than Date, and then go back to resort by date chronologically after.

Thanks,

Ryan

Simplest method would be to split the data into two columns.

If you use the Data/Text to Columns wizard, with "-" as the delimiter, and
specify the cell format as MDY, your data will be split and converted to real
dates. You can then use the normal sort tools.

If that is not an option, I would put a hidden column adjacent to your table;
use a worksheet function to return, for example, the starting date, and then
sort on that.
--ron