Help comparing ordering dates stored as text AND dates stored as custom dates

[qapla47]

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:

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 Sub

Thanks for any help!

 

[oldbrewer]

why not enter the "text" date as a real date formatted to dd/mm/yy ?

 

[qapla47]

Doing that, would you be able to suggest how to use conditional formatting to change the appearance of the date type? This is in part, why we have it formatted differently than the other dates.
The Text formatted dates have color applied to them, in addition to being formatted differently from the other dates (again, this is to be a quick view way of knowing which things are driven by customer dates and which are a matter of convenience)

I'm currently using this formula to conditionally format the text dates:
=(and(istext(E2),(and(value(E2)>=Today()+2, value(E2)<=Today()+7)))

while I'm using the format only cells that contain [Cell value between =today()+2 and today()+7] to conditionally format the "date" dates.

 

[oldbrewer]

am I right in thinking you might put a delivery date in a cell, then later customer stipulates a different date in that cell

if so .....say A1 is where you put the date and B1, at present blank, is for any date customer might stipulate

so C1 can be defined as =if(B1="",A1,B1)

now use whatever date is in C1 and if you like apply conditional formatting to C1 =B1<>""

 

[qapla47]

It is a production schedule, yes, which currently has 17 columns of information relating to the job. Due date is column 5. There are also several macros that look at the data, or the date, and adjust certain aspects of it (sorting, applying headers, formatting information, applying borders)
Due dates can either be stipulated by the customer, creating a "hard date" that a job MUST be completed by, or the date can be assigned by us, creating a "soft date" based solely on our schedule of jobs.
We indicate the difference by using text dates, which have purple text and format into that 5/5/17 format (see the last line) for the Hard Dates. Soft dates are excel date formats which have colors that change based on the background color of the cell. The background cell color changes in relation to the time remaining before the due date (in a rough stop light format, with the addition of black for past due).

[TABLE="width: 2068"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col span="2"><col span="4"></colgroup><tbody>[TR]
[TD]Job #[/TD]
[TD]Client[/TD]
[TD]Description[/TD]
[TD]Qty[/TD]
[TD]Due Date[/TD]
[TD]Stock Ord[/TD]
[TD]Stock in[/TD]
[TD]Co.[/TD]
[TD]Press/Impo[/TD]
[TD]Ink Plated[/TD]
[TD]Status[/TD]
[TD]Changes[/TD]
[TD]Bindery / Outside Services / Notes[/TD]
[TD]Last Contact[/TD]
[TD]Priority[/TD]
[TD]Current[/TD]
[TD]Next[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]HOLD[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[TD]Hold[/TD]
[TD]Hold[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20516[/TD]
[TD]Liberty Pumps 150 out 4/21[/TD]
[TD]French APB[/TD]
[TD]1500[/TD]
[TD]pt 4/21[/TD]
[TD]tagged[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]R5 3 forms s/w[/TD]
[TD]4/4[/TD]
[TD]On Hold[/TD]
[TD]01 May[/TD]
[TD]BOOKLET, 3H & shrink-wrap[/TD]
[TD="align: right"]21 Apr[/TD]
[TD] [/TD]
[TD]Hold[/TD]
[TD]Delivery[/TD]
[TD="align: left"]

<tbody>
</tbody>

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="colspan: 2"]DIGITAL PRE-PRESS[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[TD]D. Pre-Press[/TD]
[TD]D. Pre-Press[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20748[/TD]
[TD]Once Again Nut Butter[/TD]
[TD]Circle labels[/TD]
[TD]2500[/TD]
[TD]12-May[/TD]
[TD]4-May[/TD]
[TD]10-May[/TD]
[TD]Online[/TD]
[TD]Digital[/TD]
[TD]4/0[/TD]
[TD]Need PDF Proof[/TD]
[TD] [/TD]
[TD]rebox[/TD]
[TD="align: right"]05 May[/TD]
[TD="align: right"]1[/TD]
[TD]D. Pre-Press[/TD]
[TD]Digital Press[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20754[/TD]
[TD]McCormack Prod[/TD]
[TD]Business Card Magnets[/TD]
[TD]2000[/TD]
[TD]19-May[/TD]
[TD]Biz Mag[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Biz Mag[/TD]
[TD]4/0[/TD]
[TD]Need PDF Proof[/TD]
[TD] [/TD]
[TD]Biz Mag to produce[/TD]
[TD="align: right"]05 May[/TD]
[TD="align: right"]2[/TD]
[TD]D. Pre-Press[/TD]
[TD]Digital Press[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20755[/TD]
[TD]Rotork[/TD]
[TD]Business Cards & Flyers[/TD]
[TD]1m / 600[/TD]
[TD]12-May[/TD]
[TD]house[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Digital[/TD]
[TD]4/0 4/4[/TD]
[TD]Need PDF Proof[/TD]
[TD] [/TD]
[TD]trim[/TD]
[TD="align: right"]05 May[/TD]
[TD="align: right"]3[/TD]
[TD]D. Pre-Press[/TD]
[TD]Digital Press[/TD]
[TD][/TD]
[TD="colspan: 2"]

<tbody>
[TD="align: left"]

[/TD]

[TD="align: left"]

[/TD]

</tbody>

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20753[/TD]
[TD]Morgan Mgmt[/TD]
[TD]Floor Plans (8) and labels[/TD]
[TD]100/360[/TD]
[TD]11-May[/TD]
[TD]5-May[/TD]
[TD]10-May[/TD]
[TD]Online[/TD]
[TD]Digital[/TD]
[TD]4/0[/TD]
[TD]Need PDF Proof[/TD]
[TD] [/TD]
[TD]trim[/TD]
[TD="align: right"]07 May[/TD]
[TD="align: right"]4[/TD]
[TD]D. Pre-Press[/TD]
[TD]Digital Press[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20753[/TD]
[TD]Morgan Mgmt[/TD]
[TD]The Archer Referral Labels[/TD]
[TD]20 shts[/TD]
[TD]11-May[/TD]
[TD]check[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Digital[/TD]
[TD]4/0[/TD]
[TD]Need PDF Proof[/TD]
[TD] [/TD]
[TD]rebox[/TD]
[TD="align: right"]07 May[/TD]
[TD="align: right"]5[/TD]
[TD]D. Pre-Press[/TD]
[TD]Digital Press[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20696[/TD]
[TD]Morgan Management[/TD]
[TD]University Green Floor Plans (6ver)[/TD]
[TD]100ea[/TD]
[TD]2-May[/TD]
[TD]house[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Digital[/TD]
[TD]4/0[/TD]
[TD]Proof[/TD]
[TD] [/TD]
[TD]trim[/TD]
[TD="align: right"]27 Apr[/TD]
[TD] [/TD]
[TD]D. Pre-Press[/TD]
[TD]Digital Press[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20745[/TD]
[TD]Morgan Management[/TD]
[TD]Perrys Crossing trifold[/TD]
[TD]250[/TD]
[TD]10-May[/TD]
[TD]house[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Digital[/TD]
[TD]4/4[/TD]
[TD]Proof[/TD]
[TD] [/TD]
[TD]trim, score, fold[/TD]
[TD="align: right"]04 May[/TD]
[TD] [/TD]
[TD]D. Pre-Press[/TD]
[TD]Digital Press[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20727[/TD]
[TD]NE Sealcoating[/TD]
[TD]3pt NCR Proposal Form[/TD]
[TD]1000[/TD]
[TD]12-May[/TD]
[TD]5-May[/TD]
[TD]8-May[/TD]
[TD]LM[/TD]
[TD]Digital[/TD]
[TD]4/0[/TD]
[TD]Proof 2[/TD]
[TD]Made[/TD]
[TD]trim, pad for NCR[/TD]
[TD="align: right"]05 May[/TD]
[TD] [/TD]
[TD]D. Pre-Press[/TD]
[TD]Digital Press[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20717[/TD]
[TD]YMCA[/TD]
[TD]OneYNation postcard mailing[/TD]
[TD]36,000[/TD]
[TD]16-May[/TD]
[TD]R5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]R5/Digital / Mail[/TD]
[TD]1/0[/TD]
[TD]Wait for R5[/TD]
[TD] [/TD]
[TD]trim to four up blocks for C7 run[/TD]
[TD="align: right"]27 Apr[/TD]
[TD] [/TD]
[TD]D. Pre-Press[/TD]
[TD]Digital Press[/TD]
[TD][/TD]
[TD="colspan: 2"]

<tbody>
</tbody>

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20681[/TD]
[TD]Klein Steel [/TD]
[TD]Catalog 158 p + 13 tabs + cover[/TD]
[TD]500[/TD]
[TD]23-May[/TD]
[TD]order[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Digital / R5[/TD]
[TD]4/4[/TD]
[TD]Proof 2[/TD]
[TD] [/TD]
[TD]trim, collate, wire bind[/TD]
[TD="align: right"]02 May[/TD]
[TD] [/TD]
[TD]D. Pre-Press[/TD]
[TD]Digital Press[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]DIGITAL PRESS[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[TD]Digital Press[/TD]
[TD]Digital Press[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20728[/TD]
[TD]Parlec[/TD]
[TD]Omega Sales Flyer[/TD]
[TD]500[/TD]
[TD]5/8[/TD]
[TD]house[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Digital[/TD]
[TD]4/4[/TD]
[TD]Approved[/TD]
[TD]07 May[/TD]
[TD]trim[/TD]
[TD="align: right"]07 May[/TD]
[TD="align: right"]3[/TD]
[TD]Digital Press[/TD]
[TD]Bindery[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[oldbrewer]

if it were me I would have a helper column with C meaning customer stipulated and S meaning soft date - then all conditional formatting can be driven by that one cell...

 

[qapla47]

Thank you for helping me see that I was making this un-necessarily complicated.

 

[oldbrewer]

we are all learning all the time..........