Hi All,
I have a spreadsheet which re-orders on a few different criteria, but need help modifying my sort on date (my third level sort) to allow for both dates stored as text as well as the dates stored as actual date formats.
We use both formats to indicate different requirements:
Text format means a must deliver by date stipulated by the customer (Shows as 5/5/17, number format text)
Date formats mean a delivery date we assigned to the job (Shows as 5-May, standard excel date, number format custom)
I also have conditional formatting set to work for both text and date in that column.
Here's my current code:
Thanks for any help!
I have a spreadsheet which re-orders on a few different criteria, but need help modifying my sort on date (my third level sort) to allow for both dates stored as text as well as the dates stored as actual date formats.
We use both formats to indicate different requirements:
Text format means a must deliver by date stipulated by the customer (Shows as 5/5/17, number format text)
Date formats mean a delivery date we assigned to the job (Shows as 5-May, standard excel date, number format custom)
I also have conditional formatting set to work for both text and date in that column.
Here's my current code:
Code:
Sub SortLocationThenPriority()
'
' SortLocationThenPriority Macro
'
'
Cells.Select
ActiveWorkbook.Worksheets("6.23").Sort.SortFields.Clear
'First sort on the Location of the job, held in Col P
ActiveWorkbook.Worksheets("6.23").Sort.SortFields.Add Key:=Range("P2:P212"), _
SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
"Hold,D. Pre-Press,Digital Press,GS6000,Pre-Press,DI,R5,Die Cut,Bindery,H Assem.,Outside,Delivery,Billing" _
, DataOption:=xlSortNormal
'Second sort on the Priority level of the job, held in Col O
ActiveWorkbook.Worksheets("6.23").Sort.SortFields.Add Key:=Range("O2:O212"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
'Third sort on the Due Date of the job, held in Col E
'Here is where I need help, comparing both cells with "text dates" and "Excel Dates" to the current date
'But without changing
ActiveWorkbook.Worksheets("6.23").Sort.SortFields.Add Key:=Range("E2:E212"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("6.23").Sort
.SetRange Range("A1:R212")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End SubThanks for any help!
