Date counter and then stop when new date is entered

Report
Posts
1
Registration date
Monday February 24, 2020
Status
Member
Last seen
February 24, 2020
-
Posts
2656
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 17, 2020
-
I am attempting to calculate the time between B9 and Z9. and the same with D9 and Z9. the person who created the file is only calculating from "Today's" date in D4 and while that is necessary I need it to stop counting when Z9 and been entered.
So basically I need it to count days between B9 and D4 until Z9 has been entered with it's date and then stop counting.
I hope that makes sense. Any help is appreciated.

1 reply

Posts
2656
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 17, 2020
440
Hi Slizott,

Slightly confusing. First you want to calculate time between B9 and Z9, D9 and Z9. And later you say you want to calculate between B9 and D4 until Z9 is entered.

I guess the Z9's in the first sentence are a typo and you meant to say D4.

Assuming you want to use column AA and AB for the day differences, check the following code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("Z")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub

Range("AA" & Target.Row).Value = Range("D4").Value - Range("B" & Target.Row)
Range("AB" & Target.Row).Value = Range("D4").Value - Range("D" & Target.Row)
End Sub


To implement the code, right-click the sheet's tab and select View code. Paste the code in the big white field of the newly opened window. This window can be closed immediately after.

Best regards,
Trowa
1
Thank you

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 2855 users have said thank you to us this month