?
Solved

Sort data according to date and time

Posted on 2010-11-16
8
Medium Priority
?
246 Views
Last Modified: 2012-05-10
Hi Experts,

I would like to request Experts help create a macro to sort data at Column D. The nearest date and time need to be at top of the column and highlight the row (Range A:I) with red  color if the date at column D is less than 3 days (compare with current date) automatically. Hope Expert will help me to create this feature.

I have attached the workbook for Experts perusal.


SortDate.xls
0
Comment
Question by:Theva
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 20

Assisted Solution

by:Ardhendu Sarangi
Ardhendu Sarangi earned 600 total points
ID: 34152366
Hi Theva,
did you mean something like this?
Sub Macro1()
'
' Macro1 Macro
'

'
    Range("D4:D1800").Interior.ColorIndex = "0"

    ActiveWorkbook.Worksheets("Record").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Record").Sort.SortFields.Add Key:=Range("D4:D1800") _
                                                          , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Record").Sort
        .SetRange Range("A3:AP1800")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    For i = 4 To Cells(65536, "D").End(xlUp).Row
        'MsgBox (Range("D" & i) - Now())
        If Range("D" & i) - Now() > 3 Then
            Range("D" & i).Interior.ColorIndex = "03"
        End If
    Next
End Sub

Open in new window

0
 
LVL 20

Expert Comment

by:Ardhendu Sarangi
ID: 34152368
BTW, what time zone do u work from... u seems to be on all the time :)
0
 
LVL 5

Expert Comment

by:dacasey
ID: 34152381
Why do you need a macro?  You can simply sort on the date column?

Also a simple conditional format would change the color.

I have attached an image to show you the conditional format
 Excel conditional format
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Theva
ID: 34152499
Hi pari123,

Thanks for the code. Perhaps my Explanation could create confusion, sorry for that. Managed to sort for date sorting, by right should "Order:=xlAscending", but not sure how to modify <3days. We have buffer 3 days, if the date crossed this 3 days rules than we need to highlight.  e.g. today (17-Nov), therefore if there is  data at cell between 18 - 20, need to highlight, ignore other date (20 >). Hope its clear.

P/s: I'm GMT+8 time actually.  
0
 
LVL 37

Assisted Solution

by:TommySzalapski
TommySzalapski earned 400 total points
ID: 34152708
Change the code to read
If Range("D" & i) - Now() <= 3 And Range("D" & i) >= Now() Then
            Range("D" & i).Interior.ColorIndex = "03"
        End If

Open in new window

0
 

Author Comment

by:Theva
ID: 34153675
Hi Tommy,

No date being highlighted. How to code "Now() + 3 day" highlight the data. If more than 3 days ignore.

0
 
LVL 4

Accepted Solution

by:
BrainB earned 1000 total points
ID: 34154051
I think you want to sort Ascending & highlight any date 1 to 3 days from now. This is the code.
'=============================================================================
'- SORT DATES IN DESCENDING ORDER.
'- HIGHLIGHT IF BETWEEN 1-3 DAYS FROM NOW
'=============================================================================
Sub DATE_SORT()
    Dim Lastrow As Long
    Dim Day1 As Date
    Dim Day3 As Date
    '------------------------------------------------------------------------
    Lastrow = Range("D65536").End(xlUp).Row
    Range("A3:A" & Lastrow).Cells.Interior.ColorIndex = xlNone
    Day1 = Now()
    Day3 = Now() + 3
    Range("A3:AP" & Lastrow).Sort Key1:=Range("D4"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    '-------------------------------------------------------------------------
    '- LOOP ROWS
    For r = 3 To Lastrow
        If Cells(r, "D").Value > Day1 And Cells(r, "D").Value <= Day3 Then
            Cells(r, "A").Interior.ColorIndex = 6
        End If
    Next
    '------------------------------------------------------------------------
End Sub
'=============================================================================

Open in new window

0
 

Author Closing Comment

by:Theva
ID: 34154089
Hi,
Thanks for the great help.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question