# Date Diff Issue in Excel VB

I think I have dates finally figured out and then the whole date diff thing is throwing me for a loop. I have a column (AW) of dates. What I want to do is determine if the date in the column is greater than 4 years in the past (in this case I'm using days so that if it's greater than 1460 days) then to enter the number 1 44 columns to the right of the date in the cell. My dates are formatted in column AW as MM-DD-YYYY. I expect I'm having a problem with not seting a date specificaion type but unsure really how to do it. I've complicated my code so much that I don't even know what's right anymore and unsure if a case is better to use than a nested if then.
I think if someone can just help me figure out the syntax of
If DateDiff("d" date1,date2 ) > 1460 then
cell.Offset(0, 44) = 1

where date 1 is the date value in a cell in column AW Thanks
###### Who is Participating?

Commented:
Try out this..
``````Sub Test()
Dim DtToday As Date
Dim Dt As Date

Dim FirstRow As Long
Dim LastRow As Long
Dim CurrentRow As Long

Dim CurrentCol As Integer
FirstRow = 1
LastRow = [AW65536].End(xlUp).Row

DtToday = Int(Now)
CurrentCol = [AW1].Column
For CurrentRow = FirstRow To LastRow
Cells(CurrentRow, CurrentCol).Select
Dt = Cells(CurrentRow, CurrentCol)
If DateDiff("d", Dt, DtToday) > 1460 Then
Cells(CurrentRow, CurrentCol).Offset(0, 44) = 1
End If
Next CurrentRow
End Sub
``````
0

ConsultantCommented:
This will compare B2 and C2 (with C2 being the 'anchor' cell) and put a 1 across 44 cols from C2.

Alan.
``````

Sub Test()

Set MyCell = Range("C2")

If DateDiff("d", MyCell.Offset(0, -1).Value, MyCell) > 1460 Then

MyCell.Offset(0, 44).Value = 1

End If

End Sub
``````
0

Commented:
don't understand by "What I want to do is determine if the date in the column is greater than 4 years in the past"????

you enter first date in AW column than where is second data?

0

Commented:
Your question is not clear :
if you want to compare a cell having date value with particular date and want to put any spcific value in specific col. then use this :
if(b1-c1>1460,"put your true value","else false value")
put the above command in cell where you want the result.

Regards,

Bm Keshav

0

If you are concerned at text form dates then forcing them to dates may help:

If DateDiff("d" cdate(date1),cdate(date2)) > 1460 then
cell.Offset(0, 44) = 1

Chris
0

Author Commented:
Thanks so much for understanding what I was asking. Not only did I learn how to actually include a better way of using a cell value for a date compare (which I've incorporated into a few other subroutineson the same project) but also a different way of moving through rows in a column than I had been doing. It's these little pieces of wisdom that you gather that really help in bridging the gaps between what books teach and the practical applications.
0

Commented:
By the way, the line

Cells(CurrentRow, CurrentCol).Select

is not required.  I had inserted it for debugging purpose and forgot to remove it before posting the solution.  If you need to run the macro on a large number of rows of data, removing this line will speed up execution to a great extent.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.