Converting mm/dd/yyyy to yyyy/mm/dd
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 |
Converting mm/dd/yyyy to yyyy/mm/dd
|
Converting mm/dd/yyyy to yyyy/mm/dd
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. -- David Biddulph wrote in message ... 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 |
Converting mm/dd/yyyy to yyyy/mm/dd
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 |
Converting mm/dd/yyyy to yyyy/mm/dd
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). -- David Biddulph wrote in message ... 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 |
Converting mm/dd/yyyy to yyyy/mm/dd
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 wrote in message ... 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 |
Converting mm/dd/yyyy to yyyy/mm/dd
|
All times are GMT +1. The time now is 03:46 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com