Check if Date within this week/last week
 

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

Check if Date within this week/last week
 

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

Check if Date within this week/last week
 

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

Check if Date within this week/last week
 

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

Check if Date within this week/last week
 

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