• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

If date is this week, next week, last week...

W

Wulluby

Member
Hi,


Can anyone give me any pointers on how I can find if a date is either in this week, next week, beyond next week, last week or beyond last week?


I keep wanting to use something like =if(r ,9) = weeknum then do something but that isn't doing it. I've tried putting it as an if formula in a cell to test, something like =IF(I2=weeknum,"yes","no") and variations of.


The idea is that I have rows of data and I want to use a macro to filter out row by row depending on the date in 1 column. So if that date is in this week that row will be copied into the 'This Week' sheet and same for the others.


Thanks in advanced.
 
Hi, Wulluby!


Try this:

=WEEKNUM(A1,XXX)-WEEKNUM(TODAY(),XXX)


WEEKNUM function accept the following second parameter values (you can get it in your own language from within formula wizard):

-----

[pre]
Code: 
Tipo		La semana empieza el	Sistema
1 u omitido	Domingo			1
2		Lunes			1
11		Lunes			1
12		Martes			1
13		Miércoles		1
14		Jueves			1
15		Viernes			1
16		Sábado			1
17		Domingo			1
21		Lunes			2
[/pre]
-----


So replace XXX in previous formula for the chosen value, and you'll get an offset week number reference: ..., -1 previous week, 0 this week, 1 next week, ...


Regards!
 
It might be the time of night here but I'm not quite following you.


If I have a column beside the date column and put:

=IF(WEEKNUM(I2,1)-WEEKNUM(TODAY(),1),"True","False")

I get the following:


02-Apr-12 True

14-Mar-12 True

20-Mar-12 True

21-Feb-12 True

06-Feb-12 True

05-Mar-12 True

02-Feb-12 True

23-Jan-12 True

12-Mar-12 True

26-Mar-12 False


If I change any of the XXXs to 0 or -1 I get a #NUM! error. Should the last xxx always stay at 'Return Type' which returns as this week (I assume that to be 1?), then the first xxx be modulated up or down to give something from last week, next week, anything greater than next week or anything before last week?
 
Hi, Wulluby!


It might be the time of night or any liquid other than water within a glass near you :) ... who knows!


Let's try this:

a) the table I copied is used for Excel to change the day in which the week starts, as you can make it start on any day of the week, it's up to you

b) if you want your week to start on Sunday use 1, on Monday use 2: let's assume you chose on Sunday.

c) assuming your dates are in column I from row 2 in advance (as in your example with date in I2), the posted formula:

=WEEKNUM(A1,XXX)-WEEKNUM(TODAY(),XXX)

will then be in J2 as it follows:

=WEEKNUM(I2,1)-WEEKNUM(TODAY(),1)

d) then copy down J2 as needed


Just advise how it goes on.


Regards!
 
Ahhhh, and now the penny drops.


Nothing stronger than orange juice for me on a schoolnight ;)


That's perfect for what I want, thank you JB. I'm owe you an orange juice :)
 
Hi, Wulluby!


Glad to help you. But... ehmmm... is there any chance to change the orange juice for let's say a beer? It's because of citric's acid, you know...

;)


Regards!
 
A beer it is :)


Is WEEKNUM a function that can be used in vba? I'm trying to use it something like:


numRows = ActiveCell.CurrentRegion.Rows.Count

For r = 2 To numRows


strVar = Worksheets("Var Sheet").Cells(r, 9).Value


If strVar = "WeekNum(9, 1) - WeekNum(TODAY(), 1) = 0" Then

MsgBox "This Week"

Else

MsgBox "Not this week"


End If


The message box will eventually be replaced with something that will hopefully copy that row to a worksheet depending on if it is this week, last week, next week etc.
 
Hi ,


There is a subset of the Excel worksheet functions which you can use from within VBA. The syntax for this is :


Application.WorksheetFunction.the_Excel_worksheet_function


When you enter the "." after Application.WorksheetFunction , you will get a list of the Excel worksheet functions that you can use.


Narayan
 
Hi, Wulluby!


NARAYANK991 loves orange juice, so don't spend a beer with him, keep it for my next post.

;)


Regards!
 
How would you like to up that beer to a tequila?


I'm just realising that anything pre January of this year is actually appearing in my 'Future' sheet which catches everything further than next week rather than the 'Past' sheet which is used to catch everything from before last week.


Is there a way to incorporate a year "awareness"?


At the moment I am using a


cal = Application.WorksheetFunction.WeekNum(strStart, 1) - Application.WorksheetFunction.WeekNum(Date, 1)


with a


If cal = 0 Then

ElseIf cal = 1 Then

ElseIf cal > 1 Then

ElseIf cal = -1 Then

ElseIf cal < -1 Then


to move the data into either 'This week', 'Next week', 'Future', 'Last Week' or 'Past' sheet.
 
Hi, Wulluby!

After reading only your first line, my answer is "yes, anyhow".

Now I'll go on reading, excuse me.

Regards!
 
Hi, Wulluby!

So long...

WEEKNUM function retrieves a value within the date's year, it doesn't handle inter year options.

I don't imagine how to control future vs. past or next vs. last in a different way as with your cal assignment statement and the following if-elseif-endif group, except replacing it with a select-case group, but it's the very same thing.

Thanks for the chance of upgrading, but I think I still deserve just the beer.

Regards!
 
Will mess about with it but I imagine in the short term the fix may lie in something to do with


ElseIf cal > 1 OR (find some year calc that will decide if it is forward of this year) Then

Move to Future


ElseIf cal < -1 OR (find some year calc that will decide if it before this year) Then

Move to Past


Though I think it will take something a bit more complicated for this to work when it's being run at the end of December, beginning of January.


Worst case scenario I just have it check if it is week 1 or 52 and if answer Yes trigger a message box which tells the user to check 'Past' and 'Future' for actual 'Last Weeks' or actual 'Next Weeks'.
 
Hi, Wulluby!


You're right, end of year will be a little messy for WEEKNUM. So abandon WEEKNUM and go for DATE.

Let's say you have the number (long) of the date corresponding to the first day of this week stored in the variable lngThisWeekFirstDay and the same for the tested week in lngWhichWeekFirstDay.

You can try something like this:

-----

[pre]
Code: 
Select case lngWhichWeekFirstDay - lngThisWeekFirstDay
case <-7
' past
case <0
' last
case <+7
' this
case <+14
' next
case else
' future
End Select
[/pre]
-----


What do you think?


Regards!
 
You must log in or register to reply here.
Back
Top