VBA to count number of days between 2 dates

[steve400243]

Hello, I use this code to have the date and time populate in column c, starting in C13.

I would like it to also count the number of days between this date, and a date in cell B10, in cell D13. B10 would stay the same, Column C would be different dates.

Not sure if this code could be amended, or if a new one would be needed. Thank you for your help.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("B13:B5000")) Is Nothing Then
        With Target(1, 2)
        .Value = Date & " " & Time
        .EntireColumn.AutoFit
        End With
    End If
End Sub

 

[jmacleary]

Hi there. Try this (one line added in red):

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("B13:B5000")) Is Nothing Then
        With Target(1, 2)
        .Value = Date & " " & Time
        .EntireColumn.AutoFit
        End With
[COLOR=#b22222]        Target(1, 3).Value = Date - Range("B10").Value
[/COLOR]    End If
End Sub

 

[steve400243]

Hello jmacleary, thanks for looking at this - That gives me "run time error '1004': Application-defined or object defined error"

 

[jmacleary]

Is your worksheet or part of it (column D in particular) protected? Also, I edited the post immediately after I first created it - check you are using the line in red above.

 

[steve400243]

You are correct, thank you - working as needed now. Really appreciate you looking at it.

 

[steve400243]

Is your worksheet or part of it (column D in particular) protected? Also, I edited the post immediately after I first created it - check you are using the line in red above.

Hello Again jmacleary, thanks again for your help. Was wondering if the code can be changed to update the days in column D, if the Date is changed in Cell B10? Currently if that date is changed it does not update the days.

 

[jmacleary]

Hello Steve.

Sorry I've not replied sooner, I've been away for a while. Anyway, you can achieve the result with a formula in column D. In cell D13 put: '=IF(ISBLANK(C13),"",INT(C13)-$B$10)' and drag down. So, reset your change code to what it was before, and whenever C13 changes, all your column D values will also change.

 

[steve400243]

Thank you jmacleary, I appreciate the follow up. I got it working with some VBA, but will use the cell formula also and test both.

Range("D15:D" & Lastrow).Value = Range("b12").Value - Date
    End If
End Sub