Add 30 days to if criteriaClosed

Question

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

venkat1926 · Answer

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

Add 30 days to if criteria

1 response

Similar discussions

Subscribe To Our Newsletter!