0

I am currently working on a sheet, specifically column that contains various data types; where I am interested in calculating difference between the first date occured in the column and the next one to it (because the main problem is that the data in the column is heterogeneous and I am interested just in succession ).

So for each cell in the range I have to check whether is a Date , and if so calculate difference between the actual date and the that of the next cell.

I have tried some code but all I can say it is not stable since the IsDate function is acting wierd and seems to change the outcome for different reasons but never the same. 

Sub loopDate()
    Dim rnge, cell As range
    Set rnge = range("P1:P21")

    Application.ScreenUpdating = True

    For Each cell In rnge         
        cd = cell.Value2
        If IsDate(cd) = True Then       
            If IsDate(ActiveCell.Offset(1, 0)) = True Then
                n = DateDiff("d", cd, ActiveCell.Offset(1, 0))
                    If n < 0 Then
                        MsgBox "here is a difference " & n
                    Else
                        MsgBox "normal pos diff " & n
                    End If
                Else
                    MsgBox "contenent of the this cell isnt date intIF "
                End If            
            Else
                MsgBox "contenent of the this cell isnt date outIF "       
            End If
      Next  
End Sub

I am expecting either a negative or positive number that will refer to difference between two dates and tell weather we still gave time or we already got the deadline passed.

thanks for any help or suggestions about the code above.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Gin.Freecs
  • 23
  • 3
  • I believe `.Value2` ignores the Date so you should use `.Value` instead. – Damian Mar 26 '19 at 17:36
  • I corrected the code formatting. There is an End If missing close to the end – Sam Mar 26 '19 at 17:37
  • The cell you're targeting must have Date as the format (Ctrl-1) –  Mar 26 '19 at 17:41
  • 1
    Are you sure you want to access the ActiveCell in this line `If IsDate(ActiveCell.Offset(1, 0)) = True Then`. But what you get worng is that `IsDate(cd)` will always give false in case the cell contains a date as cd is double. – Storax Mar 26 '19 at 17:45

2 Answers2

0

try this:

Sub loopDate()
    Dim rnge, cell As Range
    Set rnge = Range("P1:P21")

    For Each cell In rnge
        If IsDate(cell) = True Then
            If IsDate(cell.Offset(1, 0)) = True Then
                n = DateDiff("d", cell, cell.Offset(1, 0))
                If n < 0 Then
                    MsgBox "here is a difference " & n
                Else
                    MsgBox "normal pos diff " & n
                End If
            Else
                MsgBox "contenent of the this cell isnt date intIF "
            End If
        Else
            MsgBox "contenent of the this cell isnt date outIF "
        End If
    Next

End Sub
W-hit
  • 353
  • 3
  • 14
0

If I understand you correctly, this is what you're trying to achieve; I have cleaned up some of the unnecessary bits and now you just have to edit DateCol and FindLastRow as is necessary.

Sub loopDate()

'Dim rnge As Range, cell As Range
Dim DateCol As Integer, FindLastRow As Single Dim i As Single

'Set rnge = Range("P1:P21")

'Application.ScreenUpdating = True


'  For Each cell In rnge
DateCol = 16
FindLastRow = 21
For i = 1 To FindLastRow

'     cd = cell.Value2



 If IsDate(Cells(i, DateCol)) Then

   If IsDate(Cells(i, DateCol + 1)) Then



                   n = DateDiff("d", Cells(i, DateCol).Value, Cells(i, DateCol + 1).Value)


                   If n < 0 Then

                     MsgBox "here is a difference " & n
                   Else
                     MsgBox "normal pos diff " & n


                   End If

   Else
        MsgBox "contenent of the this cell isnt date intIF "
   End If

 Else
    MsgBox "contenent of the this cell isnt date outIF "

 End If

 '  Next
 Next i


 End Sub
user11262103
  • 16