compare dates with in a array, is that possible?

[glarior]

I want to compare each date inside an array to the other dates inside the same array to determine which one is the earliest date and than transpose the smallest date to another column.

Is there a better way to go about this?

Still searching for the answer on the web, I have come close but still have not got it right. Your help will be greatly appreciated.

Thanks for your help

glarior

 

[Rofeu]

Hi,

do you need all the dates in the array or just the smallest?

I'd check the dates upon reading them and assign a special variable for just the smallest date.

assign the first value and check it against each following value to see if it's smaller. If yes, assign that value to the variable.

At the end of reading in all dates, just pass that variable for the smallest date to wherever you need it.




Cheers,

Roel

 

[SkipVought]

Hi,

Is your application Excel?

If so, use MIN(CellRangeOfDates) to determine the smallest.

once determined, use the MATCH function to locate.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[glarior]

Thanks for the replies, and yes this is in Excel.

I will try the MIN(CellRangeOfDAtes) and see how that comes out.

What I am doing is this...

Click button to update all the dates....
it determines if the date is a weekly, monthly, quarterly, semi-annual, and annual. Than it compares it to today's date and if it has past it will update to the next week, or month quaterly, etc.

The part I am asking help on is when it updates everything I want to be able to determine when the next maintenence date is due. I was thinking of running a loop and read in each row of dates, there will be five of them, weekly, monthly etc etc. I want to determine which date is the closest to todays date and than put it elsewhere on the excel spreadsheet and that will be the next preventative maintenence date.

Once again I am thankful for your help and time

glarior

 

[SkipVought]

How do you determine if a date is, "weekly, monthly, quarterly, semi-annual, and annual"???


Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[glarior]

This is the code I have so far its not perfect I have not gone through it to make it really nice yet..
and I use an array and case to determine what the weekly, monthly etc type

thanks
glarior

Private Sub CommandButton1_Click() 
    'Application.ScreenUpdating = False 
    Cells.Select                                'Center Cells 
        With Selection 
            .HorizontalAlignment = xlCenter 
            .VerticalAlignment = xlBottom 
            .WrapText = False 
            .Orientation = 0 
            .ShrinkToFit = False 
            .MergeCells = False 
        End With 
    Selection.Interior.ColorIndex = xlNone      'Clear Color in Cells 
    
    Columns("A:A").ColumnWidth = 15             'Changes Column Width 
    Columns("B:B").ColumnWidth = 15 
    Columns("C:C").ColumnWidth = 15 
    Columns("D:D").ColumnWidth = 15 
    Columns("E:E").ColumnWidth = 15 
    Columns("F:F").ColumnWidth = 15 
    Columns("G:G").ColumnWidth = 8 
    Columns("H:H").ColumnWidth = 15 
    
    Range("A1").Select                          'Reference Starting Point 
                          
    Dim Weekly As Variant                       'Define Variables 
    Dim monthly As Variant 
    Dim quaterly As Variant 
    Dim semi As Variant 
    Dim annual As Variant 
    
    Dim Today As Date 
    Dim LookRow As Integer 
    Dim IntervalType As String 
    Dim Number As Integer 
    
    Dim PmDate As Variant 
    Dim Msg 
    Dim testVariable As String 
    Dim lookColumn As String 
    Dim PmType 
    
    
    Today = Date                        'Get current Date 
    Weekly = Range("B3:B5").Value      'Get values and store in array 
    monthly = Range("C3:C5").Value 
    quaterly = Range("D3:D5").Value 
    semi = Range("E3:E5").Value 
    annual = Range("F3:F5").Value 
    storeArray = Array("", "Weekly", "Monthly", "quaterly", "semi", "annual") 
    LookRow = 3 
    
    
    For i = 1 To UBound(storeArray) 
        
        testVariable = storeArray(i) 
        
        Select Case testVariable 'declare what type of sink processes need to generate PM 
            Case Is = "Weekly" 
                lookColumn = "B" 
                LookRow = 3 
                IntervalType = "d"   '"d" specifies months as interval 
                Number = 7 
                PmType = Weekly 
                
            Case Is = "Monthly" 
                lookColumn = "C" 
                LookRow = 3 
                IntervalType = "M"   '"d" specifies months as interval 
                Number = 1 
                PmType = monthly 
                
            Case Is = "quaterly" 
                lookColumn = "D" 
                LookRow = 3 
                IntervalType = "M"   '"d" specifies months as interval 
                Number = 3 
                PmType = quaterly 
                
            Case Is = "semi" 
                lookColumn = "E" 
                LookRow = 3 
                IntervalType = "M"   '"d" specifies months as interval 
                Number = 6 
                PmType = semi 
                
            Case Is = "annual" 
                lookColumn = "F" 
                LookRow = 3 
                IntervalType = "M"   '"d" specifies months as interval 
                Number = 12 
                PmType = annual 
                
            End Select 
            
            For x = 1 To UBound(PmType) 
                For Each c In Range(lookColumn & LookRow) 
                
                If Today <= PmType(x, 1) Then 
                    'change color 
                    Range(lookColumn & LookRow).Interior.ColorIndex = 35 
                    Range(lookColumn & LookRow).Value = Application.WorksheetFunction.Transpose(PmType(x, 1)) 
                    Msg = PmType(x, 1) 
                    
                    
                ElseIf Today > PmType(x, 1) Then 
                    
                   'update Days 
                   
                   Msg = DateAdd(IntervalType, Number, PmType(x, 1)) 
                   Range(lookColumn & LookRow).Value = Application.WorksheetFunction.Transpose(Msg) 
                
                End If 
                
                Next 
                PmDate = Range("H3:H5").Value 
                For q = 1 To UBound(PmDate) 
                
                    
                    If PmDate(q, 1) > PmType(q, 1) Then 
                        Range("H" & LookRow).Value = Application.WorksheetFunction.Transpose(PmType) 
                    End If 
                Next 
                
                
                
                
                
                
                
                
                
                'If PmDate > Msg Then        'Determines the next PM Date 
                    'Range("H" & LookRow).Value = Application.WorksheetFunction.Transpose(PmDate) 
                    
                'ElseIf PmDate <= Msg Then 
                    'Range("H" & LookRow).Value = Application.WorksheetFunction.Transpose(Msg) 
                    
                
                
                LookRow = 1 + LookRow       'add 1 to advance to the next row 
                
                
            Next 
        
    
    Next 






End Sub

 

[glarior]

Wow, don't ask me why I posted the whole thing like that, wish I could delete that post.

I saw I was missing my comments... first thing I saw was
"d" specifies months as interval " on everyone one which is not true, weekly is "D" and the rest is "m"

first for loop determines if it should change the color or update the date for each cell

The second for loop is where I am having the problem as I said earlier. I was experimenting with it but no luck.

and finally the last 'if' statement was what I tried the very first time and I should have deleted it before I posted, sorry about that. I won't be in a hurry to post again

Sorry for posting all that crap.... I am still looking at the MIN(CellRangeOfDates) and MATCH thing by skip.. thanks

glarior

 

[SkipVought]

Do you have REAL DATES???

Why do Dates and Times seem to be so much trouble? faq68-5827

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[glarior]

Yea, I have real dates that were pre-entered. Good article on the FAQ. I have not had the time to play with the MIN(CellRangeOfDates), infact when I tried to find it under the help system it threw an error saying the help file is not on the computer. That won't stop me from finding it though. Thanks again
glarior