Hi All,

I am having trouble figuring out a way to go through a range and
determine if each cells date content was this week or last week etc,
What I have so far looks if it is equal to today but I want to change
it so it looks if the date is coming up within a week and I will have a
seperate button to see if it was within last week.

I have so far:

For Each cl In Range("D1:D1000")
If cl.Value = Date Then
MsgBox "Description = " & cl.Offset(0, -3).Value & vbCrLf &
"Location = " & cl.Offset(0, -2).Value & vbCrLf & "Date of Next Test =
" & cl.Value, vbCritical + vbExclamation
End If
Next
End Sub


Now the above works perfectly, but I dont have a clue how I would now
go on to say is the date (cl.value) coming up in seven days time?

Any help at all would be much appreciated.

Many thanks

Duncan

Duncan wrote:

For Each cl In Range("D1:D1000")
If cl.Value = Date Then

Now the above works perfectly, but I dont have a clue how I would now
go on to say is the date (cl.value) coming up in seven days time?


Excel stores dates as numbers, and a day is equal to '1'. Forward time
= larger numbers

So, saying "cl.value is less than 7 days from now" is exporessed as

if cl.value < date + 7

this will also find all dates in the past, which we may not want. 'In
the next week' becomes
if cl.value < date + 7 and cl.value <= date

HTH, post back if not.

Nik

Hi Nik,

Thank you, that does work to a fashion but also brings back ones where
the date is previous to today, I tried reversing the "<" to "" but
didnt seem to work

I will play about with it.......


Nik wrote:
Duncan wrote:

For Each cl In Range("D1:D1000")
If cl.Value = Date Then

Now the above works perfectly, but I dont have a clue how I would now
go on to say is the date (cl.value) coming up in seven days time?


Excel stores dates as numbers, and a day is equal to '1'. Forward time
= larger numbers

So, saying "cl.value is less than 7 days from now" is exporessed as

if cl.value < date + 7

this will also find all dates in the past, which we may not want. 'In
the next week' becomes
if cl.value < date + 7 and cl.value <= date

HTH, post back if not.

Nik

Hi Nik,

Have got it working with your post, I was being slightly dim earlier!

For all of this weeks I have got:
if cl.value < date + 7 and cl.value = date

For all of last weeks I have got
if cl.value date - 7 and cl.value < date

Duncan wrote:
Hi Nik,

Thank you, that does work to a fashion but also brings back ones where
the date is previous to today, I tried reversing the "<" to "" but
didnt seem to work

I will play about with it.......


Nik wrote:
Duncan wrote:

For Each cl In Range("D1:D1000")
If cl.Value = Date Then

Now the above works perfectly, but I dont have a clue how I would now
go on to say is the date (cl.value) coming up in seven days time?


Excel stores dates as numbers, and a day is equal to '1'. Forward time
= larger numbers

So, saying "cl.value is less than 7 days from now" is exporessed as

if cl.value < date + 7

this will also find all dates in the past, which we may not want. 'In
the next week' becomes
if cl.value < date + 7 and cl.value <= date

HTH, post back if not.

Nik

Duncan wrote:

this will also find all dates in the past, which we may not want. 'In
the next week' becomes
if cl.value < date + 7 and cl.value <= date


Thank you, that does work to a fashion but also brings back ones where
the date is previous to today, I tried reversing the "<" to "" but
didnt seem to work

Sorry, my mistake.

Try playing with changing the <= to = - this will restrict to dates
today or future. Combined with < date + 7, this gives you what you want.

Nik