Solved: Date functions

[samuelwright]

Hello all

I am trying to compare 2 columns of dates in a spreadsheet and if the second column is 3 months older than the first column, then it changes the background colour. I have the following, but to no avail! Please help!

[VBA] Private Sub openbutton_click() ' the code below happens whenever the button is clicked
'Declarations
Dim i As Integer
Dim valueA As Date
Dim valueB As Date
For i = 1 To 6
valueA = Sheet1.Cells(i, 4)
valueB = Sheet1.Cells(i, 3)

If valueA > valueB + 90 Then
Cells(i, 2).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With

End If
Next i
End Sub [/VBA]

I have four columns, 1st column is one date, second column is another date, then the 3rd and fourth columns are the dates again but formatted into normal numbers

Sam

[mvidas]

Hi Sam,

Rather than use VBA for this, why not just have conditional formatting do it for you, so you don't have to run this all the time.

Select column B, go to Format / Conditional Formatting. Change the dropdown to "Formula Is", and in the formula bar there enter the following:
=D1>DATE(YEAR(C1),MONTH(C1)+3,DAY(C1))

Click the Format button, go to Pattern, select yellow, and click ok, then ok again. Should be all you need!
Matt

[samuelwright]

And if I wanted to see if the column was greater than 3 months from today would I write:
=D4>TODAY(Year(D4), Month (D4) + 3, day (D4)) ?

Thanks!

[Bob Phillips]

And if I wanted to see if the column was greater than 3 months from today would I write:
=D4>TODAY(Year(D4), Month (D4) + 3, day (D4)) ?

Thanks!

No just,

=D4>DATE(YEAR(TODAY()), MONTH(TODAY()) + 3, DAY(TODAY()))

[samuelwright]

Thanks XLD, super job