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

  • Thread starter Thread starter Joeyej
  • Start date Start date
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
 
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
 
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...
 
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...
 
Back
Top