[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.excel.programming

Check if Date within this week/last week

Duncan

12/15/2006 2:03:00 PM

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
4 Answers

Nik

12/15/2006 2:14:00 PM

0

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

12/15/2006 2:25:00 PM

0

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

12/15/2006 2:37:00 PM

0

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

Nik

12/15/2006 2:38:00 PM

0

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