none
Loop through rows to determine time and day difference two dates RRS feed

  • Question

  • Hi,

    Im trying to find the time and day difference between two dates. My code is as follows:

    For v = 0 To Sheet2.Columns.Range("AT3").End(xlDown).Row
    
    
    Sheet2.Cells(3 + v, 48) = DateDiff("d", Sheet2.Cells(3 + v, 46).Value, Sheet2.Cells(3 + v, 47).Value) & " Days"
    Sheet2.Cells(3 + v, 49) = DateDiff("h", Sheet2.Cells(3 + v, 46).Value, Sheet2.Cells(3 + v, 47).Value) & " Hours"
    Sheet2.Cells(3 + v, 50) = DateDiff("n", Sheet2.Cells(3 + v, 46).Value, Sheet2.Cells(3 + v, 47).Value) & " Minutes"
    
    
    If Sheet2.Cells(3 + v, 46).Text <> "" And Sheet2.Cells(3 + v, 47).Text <> "" Then
                Date1 = CDate(Sheet2.Cells(3 + v, 46).Text)
                Date2 = CDate(Sheet2.Cells(3 + v, 47).Text)
    
                For Days = Date1 To Date2
                 DayNames = DayNames + Format(Days, "dddd")
                 Sheet2.Cells(3 + v, 51) = DayNames
                  Next
    
    
    End If
    
    Next v
    

    The above works out the time difference in Days, Hours & Minutes correctly, however when trying to output the Day Names i.e. Monday, Tuesday, Wednesday the For Loop keeps the original value from the previous row and adds it on thus I receive skewed results.

    Could someone please help on how to iterate through each loop correctly to output the day names between two dates?

    Thanks for the help,

    Jas

    Monday, February 13, 2012 3:23 PM

Answers

  • Hi,

    Ive managed to solve it, I placed a DayNames = "" after the End If and now it works.

    Thanks,

    Jas

    • Marked as answer by Jas10 Monday, February 13, 2012 3:43 PM
    Monday, February 13, 2012 3:43 PM