none
Excel VBA code comparing dates. RRS feed

  • Question

  • Hello everyone. I am new to excel VBA coding and have been having issues creating a code to compare dates in cells and today's date to have an outcome of status on an action and I am hoping someone may shed some light in what I am doing wrong.

    I don't want to bore people with unnecessary details about the rest of the code but I will try to give details about the part of the code I am having issues with. The worksheet will have actions to do in a cell, a target date for completion, a closure date when it was actually finished, and a status of where the action is at. What I want to do with the code is compare the target date with todays date and if it is greater than todays date the status would be late, if the target date is less than todays date and there is no closure date the status would come out as open, and finally if there is a closure date and it is less than or equal to the target date the status would be closed. The date insert in the cells is day/month/year. Here is part of the code I am trying to do this with, with the relevant variables. Any help would be greatly appreciated.

    Dim Status As String
    Dim TargetDate As Date
    Dim TodaysDate As Date
    Dim ClosureDate As Date
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    Set sh2 = ActiveWorkbook.Sheets("RAIL")
    row1 = sh2.Range("H" & Rows.Count).End(xlUp).row
    row2 = sh2.Range("C" & Rows.Count).End(xlUp).row
    row3 = sh2.Range("F" & Rows.Count).End(xlUp).row
    row4 = sh2.Range("G" & Rows.Count).End(xlUp).row
    TodaysDate = Date   
        For i = 7 To row3
            For j = 7 To row1
                For k = 7 To row4
                TargetDate = sh2.Cells(i, 6).Value
                ClosureDate = sh2.Cells(k, 7).Value
                Status = LCase(sh2.Cells(j, 8).Value)
                    If ClosureDate = "" & TargetDate > TodaysDate Then
                    Status = "open"
                    ElseIf TargetDate < TodaysDate Or ClosureDate > TargetDate Then
                    Status = "late"
      ElseIf ClosureDate <= TargetDate Then
                    Status = "closed"
                    End If
                Next k
            Next j
        Next i

    Sunday, March 13, 2016 5:45 PM

All replies

  • Re:  determine status of a job

    I did not look at your code as your approach may need revision.

    Current:
    Compare the target date with todays date...
       if it is greater than todays date the status would be late,
       if the target date is less than todays date and there is no closure date the status would come out as open
       if there is a closure date and it is less than or equal to the target date the status would be closed

    Should be:
    Compare dates in this order...
       if there is a closure date the status would be closed
       if target date is greater than todays date, the status would be open,
       otherwise, the status would would be late

    '---
    Jim Cone
    Portland, Oregon USA



    • Edited by James Cone Monday, March 28, 2016 2:56 PM
    Sunday, March 13, 2016 7:15 PM
  • Thanks for the suggestion here are my issues.

    I don't just want it to be closed if there is a value for the closure date. If the closure date is greater than the target date I would still like it to state it was late. This is why I have the extra conditions in my if statements and for the status of closed I have if the closure date is less than or equal to the target date. I see what you mean by the order and this is actually similar to what I have changed it to, but no matter the order it does not work. Here is my current coding please look it over and let me know anything I can change in it. I may be using the embedded for statements incorrectly.

    For i = 7 To row1
            For j = 7 To row4
                For k = 7 To row3
                TargetDate = sh2.Cells(k, 6).Value
                ClosureDate = sh2.Cells(j, 7).Value
                Status = LCase(sh2.Cells(i, 8).Value)
                    If ClosureDate = "" And TargetDate > Date Then
                    Status = "open"
                    ElseIf ClosureDate <= TargetDate Then
                    Status = "closed"
                    ElseIf TargetDate < Date Or ClosureDate > TargetDate Then
                    Status = "late"
                    End If
                Next k
            Next j
        Next i

    Monday, March 14, 2016 12:22 AM
  • Re:  still not working

    "Not working" is not very helpful.  My car is 'not working' could mean it is out out of gas or you lost your car keys.

    It appears your loops need to be told what direction to loop.
    Excel loops from a small number to a larger number automatically (one unit a a time)
    If you are looping from a large number to a smaller number then you have to provide instructions...

    For i = 7 to row1
      should be
    For i = 7 to row1 step -1
    (or step -2 if you want to skip every other row)

    Same for the other loops.
    If you loop from large to small without a step indicator then Excel exits the loop.

    '---
    Jim Cone


    Monday, March 14, 2016 1:31 AM
  • Hi Dougherty13,

    Why do you need to embed the For statement? Can you simply iterate the Target Date rows and do the comparison?

        For i = 7 To row3
            TargetDate = sh2.Cells(i, 6).Value
            ClosureDate = sh2.Cells(i, 7).Value
            Status = LCase(sh2.Cells(i, 8).Value)
                If ClosureDate = "" & TargetDate > TodaysDate Then
                    Status = "open"
                ElseIf TargetDate < TodaysDate Or ClosureDate > TargetDate Then
                    Status = "late"
                ElseIf ClosureDate <= TargetDate Then
                    Status = "closed"
                End If
            sh2.Cells(i, 8).Value = Status
        Next i


    • Edited by Jackie_ Monday, March 14, 2016 2:04 AM
    Monday, March 14, 2016 2:01 AM
  • Sorry I was not descriptive. It literally skips over this part of the code. I have another part of the code that changes the color of the row based on the company who has to complete the action. When I run the code the color change works but it skips over the code I am trying to use to compare the dates. I do not get any kind of error. It must be what you are saying it is exiting the loop, thank you.
    Monday, March 14, 2016 10:32 AM
  • Thank you for your response Jackie.

    The reason I was trying an embedded for loop was because I set each of the row1, row3 and row4 equal to a range in different columns so it will check through each column. Looking back maybe I should have used a different term to define ranges in columns lol using row seems kind of confusing.

    I tried your code just using one of the row variables and it did actually run for me, but it only checked through the one column and only would show close or late in the status and it did not always result in what I wanted for the conditions. Is there a way to combine those ranges in different columns into one variable or a way to state all of those (row1, row3 and row4) in the for loop without embedding? I think it would work the way I want it to if there is a way to do that.

    I apologize if my descriptions are not very good or if I do not completely understand responses I am still getting use to VBA. I appreciate all the help.

    Monday, March 14, 2016 12:34 PM
  • Also, Jackie.

    To help me learn more about coding I wanted to ask why you added the last line:

    sh2.Cells(i, 8).Value = Status

    What does this do? It looks similar to the earlier part in the code ( Status = LCase(sh2.Cells(i, 8).Value)), but I am not sure what adding the last line does can you please explain this to me so I can better understand?  

    Monday, March 14, 2016 12:52 PM
  • Hi Dougherty13,

    I was using

    sh2.Cells(i, 8).Value = Status

    to set the "Status" cell to one of "open"/"late"/"closed".

    Maybe this is not intended?

    Sorry I may not understand your requirement thoroughly, could you please share your Excel example file, it doesn't have to be your complete one,  as long as it contains several rows of data.

    Tuesday, March 15, 2016 3:53 PM
  • Yes, that is what I want the code to do. It will not do anything without that line, thank you.

    I have been playing with the code to try and get it to work the way I want it to. I have deleted some of the conditions and tried using separate for loops for each status outcome. However, I can only get the open status to work along with either the late or closed status. For some reason I can not get both the closed and late status to work at the same time and the one that does work comes out when the other is supposed to.

    Unfortunately my account still has to be verified so I cannot attach anything to my responses but I can copy and paste the code I am currently using. Sorry I cannot provide an example of the worksheet when my account is verified I will share it.

    Private Sub Update_Click()
        Dim i As Integer
        Dim j As Integer
        Dim k As Integer
        Dim row1 As Integer
        Dim row2 As Integer
        Dim row3 As Integer
        Dim TargetDate As Date
        Dim ClosureDate As Date
        Dim suppliername As String
        Dim customername As String
        Dim Status As String
        Dim Owner As String
        Dim sh1 As Worksheet
        Dim sh2 As Worksheet
        Set sh1 = ActiveWorkbook.Sheets("Team Members")
        Set sh2 = ActiveWorkbook.Sheets("RAIL")
        row1 = sh2.Range("H" & Rows.Count).End(xlUp).row
        row2 = sh2.Range("C" & Rows.Count).End(xlUp).row
        row3 = sh2.Range("F" & Rows.Count).End(xlUp).row
        row4 = sh2.Range("G" & Rows.Count).End(xlUp).row
      
        For i = 7 To row2
        suppliername = LCase(sh1.Range("B7").Value)
        customername = LCase(sh1.Range("B8").Value)
        Owner = LCase(sh2.Cells(i, 3).Value)
            If Owner = suppliername Then
                sh2.Range("A" & i & ":I" & i).Interior.ColorIndex = 40
            ElseIf Owner = customername Then
                sh2.Range("A" & i & ":I" & i).Interior.ColorIndex = 35
            ElseIf Owner = "us" Then
                sh2.Range("A" & i & ":I" & i).Interior.ColorIndex = 38
            End If
        Next i
        For i = 7 To row3
                TargetDate = sh2.Cells(i, 6).Value
                ClosureDate = sh2.Cells(i, 7).Value
                Status = LCase(sh2.Cells(i, 8).Value)
                    If TargetDate > Date Then
                    Status = "open"
                    ElseIf TargetDate < Date Then
                    Status = "late"
                    ElseIf TargetDate <= ClosureDate Then
                    Status = "closed"
                    End If
                sh2.Cells(i, 8).Value = Status
        Next i
    End Sub

    Tuesday, March 15, 2016 5:17 PM