How to do date calculations?

[Hondy]

Hi

Can anyone tell me how I can return only values in the last 180 days?

in laymans:

If date is between "now" and 180 days ago then...

Thanks

 

[PaulBricker]

Look at the DateAdd() function. That may do what you need.

Between Date() and DateAdd("d",-180,Date())



Paul

 

[Hondy]

Thanks, DateAdd seems to be the correct function

Dim str_rangedate
dim str_vardate

str_vardate = rs(registration_date)

str_rangedate = dateadd("d",-180, now())
if str_vardate >= str_rangedate then
...

The above code makes str_range date 180's ago according to "response.write str_rangedate"

But the if statement seems to include every record? if I switch around the >= to <= it still doesn't work as expected. I'm not a programmer, whats wrong with my code?

Thanks

 

[PaulBricker]

What value does str_vardate return? If rs(registration_date) is a date only field, then you might try using the Date() function instead of the Now() function which returns a date/time value. What does the rest of your If statement look like.

Paul

 

[Hondy]

str_vardate returns a variable datetime stamp similar to str_rangedate.

The IF statement is just this:

if str_vardate >= str_rangedate then
str_cellcolour1="orange"
else str_cellcolour1 ="green"
end if

Where the cellcolour variable changes the colour on a cell

Any ideas? I have the theory down ok it just seems to not colour in what I expect. I have done this successfully for other variables such as "amount" it's just this datetime thing causing me issues...

Thanks

 

[Hondy]

Hi Paul

This fixed it:

if CDATE(str_vardate) >= CDATE(str_rangedate)

 

[PaulBricker]

Glad you got it.

Paul