Identify if a week is within the last 5 weeks

gmazza76

Well-known Member
Joined
Mar 19, 2011
Messages
767
Office Version
  1. 365
Platform
  1. Windows
Good afternoon

Ive come up against a query I need to add into a current table so that when data is added week on week I can show a column that will identify the last 5 weeks continually no matter what week of the year it is.

I have tried using the Calculated field in my table calling it week count, but am getting stuck.

I had something in mind of
Code:
IIf([Week]<=(DatePart("w",Now()-5),5,0)))

I get an error "cannot be used in a calculated column, is there away round this?

thanks in advance
Gavin
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
would it be easier to write this in the query and re make the table at the end?
If so what would be the best formula to use to only identify the last 5 weeks based on the current date/week?

thanks
 
Upvote 0
Why are you trying to write the results back to a table?
Usually, that is not necessary, and can actually be a detriment, as it can take up extra space, cause data integrity issues, and eliminate the dynamic nature of your database.
Typically, you would just use a Query with dynamic criteria to return the last 5 weeks.

It could be as simple as subtracting 35 days (5 weeks times 7 days) form the date in the criteria.
Or maybe it has to be more complicated.

Note that one potential issue I see with the logic path you were going down is that if you go solely by week number, year is not being considered. So it could return data from prior years that have the same week numbers.
 
Upvote 0
IIf(Week<=DateAdd("w",-5,Now()),5,0)
That will only work if all the data is for the same year. If the data covers multiple years, you will also need to work some sort of year check into the criteria.
(Could be a second criteria calculation field to see if Year of Now() matches Year of date you are checking).
 
Upvote 0
Not sure if this is the best possible way to go, but it seems to work for me at the moment

Code:
Week Count: IIf([week] Between DatePart("ww",Now(),2)-1 And DatePart("ww",Now(),2)-6,1,0)
 
Upvote 0
That will only work if all the data is for the same year. If the data covers multiple years, you will also need to work some sort of year check into the criteria.
(Could be a second criteria calculation field to see if Year of Now() matches Year of date you are checking).
It is just going to produce a date value, so the year should be taken into account?
I would also use Date() instead of Now() as time element not required?
I am assuming that the badly named field called week, is actually a date? :)
 
Upvote 0
Good morning @Joe4 and @welshgasman,

I have tried the formula above and still get the error "the expression cannot be used in a calculated field).

The reason I want to add the calculation into the main table is as it will be linked to another file (excel) and the "wk5" column will auto link upto an existing setup that has been running a while. The year indicator wouldnt be an issue in this case as the data resets and runs for 1 year only. ie, Jan the data will be cleared and reset.

I would normally do this the way Joe has suggested and pull the data separately for the given period, but as several reports will be linked to the existing table it becomes a pain as I would need to rebuild everything that is setup which isnt ideal or easy as other people will use the output table.

Is there a way to write the formula as a calculated reference?

thanks
 
Upvote 0
It seems the issue is the DatePart("ww",Now()) that seems to be an issue
 
Upvote 0

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top