If date1 is x days later than date2

[Endered5]

So I have 2 dates, date1(A1) and date2(A2). Let's say A1 is 2020-11-18 and A2 is 2020-11-24. That is 6 days apart. What I want to do is, if it is 6 days apart, I want my date A2 change to one more day (2020-11-25).

Basically, I want to make sure the days between dates is 7 days(1 week).

How would I do that if I want to write the code?

 

[6StringJazzer]

If you put this code in the module for the worksheet containing these cells, then it will be triggered any time either of the cells change value and update A2 as needed.

Private Sub Worksheet_Change(ByVal Target As Range)

   If Not Intersect(Target, [A1]) Is Nothing Or _
      Not Intersect(Target, [A2]) Is Nothing Then
      If [A2] < [A1] + 7 Then [A2] = [A1] + 7
   End If

End Sub

 

[Endered5]

If you put this code in the module for the worksheet containing these cells, then it will be triggered any time either of the cells change value and update A2 as needed.

Private Sub Worksheet_Change(ByVal Target As Range)

   If Not Intersect(Target, [A1]) Is Nothing Or _
      Not Intersect(Target, [A2]) Is Nothing Then
      If [A2] < [A1] + 7 Then [A2] = [A1] + 7
   End If

End Sub

But in this case I either will have A1 = 2020-11-18 and A2=2020-11-24 (6 days apart). I want A2 to become 2020-11-25

or A1=2020-11-18 and A2 = 2020-11-19, then do nothing.

 

[6StringJazzer]

Did you try the code?

 

[Endered5]

Did you try the code?

yes, didnt seem to work? I can not find the macro

 

[Endered5]

Could I not use something like this?

Sub date()

If range("A2") - range("A1") = 6 Then
Range("V2").Value = Range("V2").Value + 1
End If


End Sub

 

[6StringJazzer]

You did not follow my instructions. This code has to go into the module for Sheet1 or Sheet2, I don't know which one you have your data in. It is intended to run automatically whenever there is a change in the data.

Your example will work if the difference is exactly 6 but if the difference is 1-5 it won't work. For your sub to be a general solution:

Sub date()

If Range("A2") < Range("A1") + 7 Then
   Range("A2").Value = Range("A1").Value + 7
End If

End Sub

 

[Endered5]

You did not follow my instructions. This code has to go into the module for Sheet1 or Sheet2, I don't know which one you have your data in. It is intended to run automatically whenever there is a change in the data.

Your example will work if the difference is exactly 6 but if the difference is 1-5 it won't work. For your sub to be a general solution:

Sub date()

If Range("A2") < Range("A1") + 7 Then
   Range("A2").Value = Range("A1").Value + 7
End If

End Sub

Hi again,

your VBA code works. However it's part of a bigger problem. Let's say I want to do a loop through all the rows. Here I compare Column "J" with Column "K". If it differs 6 days apart, I want to change column "K" so it differs 7 days. If date in column "K" is equal to column "J", I want to change column "K" so it add one day from column J.

So in this example I want to change
K2 to 2020-11-20
K3 to 2020-11-21
K4 no change
K5 to 2020-12-12

But I get error, and it does not work. Why?

Sub Datetest()

Dim rowcount1 As Integer
Dim S As Integer

    rowcount1 = Cells(Rows.Count, 10).End(xlUp).Row
   
    For S = 2 To rowcount1
If Range("J" & S) = Range("K" & S) Then
  
Range("K" & S).Value = DateAdd("d", 1, CDate(Range("K" & S)))
  
ElseIf Range("K" & S).Value = Range("J" & S).Value + 6 Then
Range("K" & S).Value = Range("J" & S).Value + 7


    End If
    Next S
  
End Sub

 

[6StringJazzer]

The code you showed is not at all related to the solution I suggested. I may still be able to help.

it's part of a bigger problem

It's always good to explain the entire problem from the start.

But I get error, and it does not work. Why?

What is the error message, and what line of code?

 

[6StringJazzer]

If Range("J" & S) = Range("K" & S) Then
 
Range("K" & S).Value = DateAdd("d", 1, CDate(Range("K" & S)))

This says if the dates are equal, add 1 day to the second date. You said you wanted it to be 7 days later. Try this:

If Range("J" & S) = Range("K" & S) Then
 
Range("K" & S).Value = Range("K" & S) + 7