Solved

Sort data according to date and time

Posted on 2010-11-16
8
237 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
8 Comments
 
LVL 20

Assisted Solution

by:pari123
pari123 earned 150 total points
Comment Utility
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:pari123
Comment Utility
BTW, what time zone do u work from... u seems to be on all the time :)
0
 
LVL 5

Expert Comment

by:dacasey
Comment Utility
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
 

Author Comment

by:Theva
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 37

Assisted Solution

by:TommySzalapski
TommySzalapski earned 100 total points
Comment Utility
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
Comment Utility
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 250 total points
Comment Utility
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
Comment Utility
Hi,
Thanks for the great help.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

763 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now