Comparing old date to now() and writing out text message in column

J

Joeyej

I'm new to Excel programming & I'm working on a [stock purchase]
spreadsheet trying to compare a column of dates (range named
"acquired") to the current date. Any acquired date over 365 days
should write a text message saying "long" in the adjacent column range
named "holding". Acquired dates less that 365 needs to write "short".

Thanks for your help.

Joe
 
D

Don Guillett

try this. Of course, modify to suit. Assumes dates in col A

Sub comparedate()
For Each c In Range("a1:a3")
If c + 365 > Now Then c.Offset(, 1) = "Long"
If c + 365 < Now Then c.Offset(, 1) = "Short"
Next
End Sub
 
T

Tom Ogilvy

if c + 365 is greater than now, then c was acquired less than 365 days ago,
so that should be "short" shouldn't it?

Probably should allow for exactly 365 days as well. (although unspecified
by the OP). Since Now has a time value, I guess it is unlikely to hit an
exactly 365 days situation.

--
Regards,
Tom Ogilvy

Don Guillett said:
try this. Of course, modify to suit. Assumes dates in col A

Sub comparedate()
For Each c In Range("a1:a3")
If c + 365 > Now Then c.Offset(, 1) = "Long"
If c + 365 < Now Then c.Offset(, 1) = "Short"
Next
End Sub

Joeyej said:
I'm new to Excel programming & I'm working on a [stock purchase]
spreadsheet trying to compare a column of dates (range named
"acquired") to the current date. Any acquired date over 365 days
should write a text message saying "long" in the adjacent column range
named "holding". Acquired dates less that 365 needs to write "short".

Thanks for your help.

Joe
Click to expand...
Click to expand...
 
D

Don Guillett

Yeah, I guess OP can change to suit.

Tom Ogilvy said:
if c + 365 is greater than now, then c was acquired less than 365 days ago,
so that should be "short" shouldn't it?

Probably should allow for exactly 365 days as well. (although unspecified
by the OP). Since Now has a time value, I guess it is unlikely to hit an
exactly 365 days situation.

--
Regards,
Tom Ogilvy

Don Guillett said:
try this. Of course, modify to suit. Assumes dates in col A

Sub comparedate()
For Each c In Range("a1:a3")
If c + 365 > Now Then c.Offset(, 1) = "Long"
If c + 365 < Now Then c.Offset(, 1) = "Short"
Next
End Sub

Joeyej said:
I'm new to Excel programming & I'm working on a [stock purchase]
spreadsheet trying to compare a column of dates (range named
"acquired") to the current date. Any acquired date over 365 days
should write a text message saying "long" in the adjacent column range
named "holding". Acquired dates less that 365 needs to write "short".

Thanks for your help.

Joe
Click to expand...
Click to expand...
Click to expand...
 
Top