1

I have a calculated column called Week Starting that calculates the date starting the current week. This week the column display 09/20/2015. I need to create a view that always shows the week before that and only that week, so essentially I would need to see the week starting 09/13/2015 and not 09/20/2015 or 09/06/2015. I can't seem to wrap my head around how I would achieve this. Any ideas? Thanks!

  • Are you doing two weeks ago from the modified date, created date, or a different field? Are you looking for items that are due between the 20th and 26th? – Jordan Sep 24 '15 at 15:17
  • This week I'm looking for items between the 13th and 19th, next week that will change to 20th through the 26th only. I'm using different field that calculates the start of the week called "Week Starting". – Ghoztrider Sep 24 '15 at 16:12
  • Oh yes, sorry screwed up the dates. – Jordan Sep 24 '15 at 16:14
2

To do this you will need to create two calculated columns and then a view using these.

Calculated Fields

I used TwoWeeksSaturday and TwoWeeksSunday for this.

The calculation for TwoWeeksSaturday is:

=IF(WEEKDAY([Due Date])="1",[Due Date]+13,[Due Date]+(13-WEEKDAY([Due Date],2)))

The calculation for TwoWeeksSunday is:

=IF(WEEKDAY([Due Date])="1",[Due Date]+7,[Due Date]+(7-WEEKDAY([Due Date],2)))

This is if we are filtering on Due Date, if you need to filter on a different field replace it accordingly.

View

Now create a view and use these filters:

TwoWeeksSunday is less than or equal to [Today]

and

TwoWeeksSaturday is greater than or equal to [Today]

This should set up an ongoing view for you to see things due two weeks ago.

Edit:

You can also use this for any set of weeks you want (3 weeks ago, 4 weeks ago, etc.) by adding/subtracting the 13 and 7 in the calculations by multiples of 7. This formula is set up for weeks to start on Sunday and run through Saturday however.

Edit:

Make sure the filter is set up like this. If the dates from the calculation are correct then this should be all there is left to do.

enter image description here

  • The calculated fields are working nicely. The WeekStarting field is 09/20/2015 and TwoWeeksSunday is 09/20/2015. TwoWeeksSaturday is 09/26/2015. In a standard view with no filtering the calculated fields display the correct date, however, after applying the filter to a view it's a blank view when there should be 4 records that fall within that time frame. I've been messing with the filters a bit to see if something comes up, no luck...Almost there... – Ghoztrider Sep 28 '15 at 13:07
  • Check edit to make sure filter is set up correctly. If you see the correct date in the calculated fields then this should be all there is left to do. – Jordan Sep 28 '15 at 13:14
  • Ok...I figured out the view format to show what I needed. I added [Today]-7 for TwoWeeksSunday and [Today]-7 for TwoWeeksSaturday to give me all reecords with week starting 09/20/2015, which of course will change next week. Thanks for getting me here Jordan! – Ghoztrider Sep 29 '15 at 13:18
4

You can create a filter on the "Created" column and use: [Today] -1 to return records/documents created yesterday (for example). If you use multiple filters, you can filter documents for -14 days ago OR -15 days ago OR -16 days ago etc etc

I hope this helps, Best, George

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.