Add 30 days to if criteria

Closed
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
Hello,

I have a small problem in Excel. I have a table where one column contains text and other date. My idea is when in a text column I put, for example, invoice, than the date column to increase 1 month from previous date. How can I make this to happen?
Example:
A B C
27.05.09 inv 27.06.09
01.06.09 inv 01.07.09


Thanks

1 reply

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
804
I suppose A1 is where you enter invoice or not. B1 is date. You cannot change B1 otheriwse it will elad to ciruclar reference. in another empty cell in the same row the following formula

=IF(A1="invoice",IF(MONTH(B1)=12,DATE(YEAR(B1)+1,1,DAY(B1)),DATE(YEAR(B1),MONTH(B1)+1,DAY(B1))),"")

if you ant to change the date in B1 itself you need a small event code
right click the sheet tab and click view code and copy paste this code


Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Target.Offset(0, 1)
If Target.Column <> 1 Then Exit Sub
If Target = "" Then Exit Sub
If Target = "invoice" Then
If Month(Target.Offset(0, 1)) = 12 Then
rng = 1 & "/" & Day(rng) & "/" & Year(rng) + 1
Else
rng = Month(rng) + 1 & "/" & Day(rng) & "/" & Year(rng)
End If
End If

End Sub


wherever you enter "invoice in column A the next entry in column B will advance by one month. do a few tests. in an empty workbook