Solved

Date Diff Issue in Excel VB

Posted on 2009-04-14
563 Views
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
0
Question by:luminent

LVL 12

Expert Comment

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

LVL 31

Expert Comment

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

LVL 6

Expert Comment

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

LVL 59

Expert Comment

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

LVL 9

Accepted Solution

felixdsouza earned 250 total points
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

Author Closing Comment

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

LVL 9

Expert Comment

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

Featured Post

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.