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!

Improve this question
3
  • 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, 2015 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, 2015 at 16:12
  • Oh yes, sorry screwed up the dates.
    – Jordan
    Sep 24, 2015 at 16:14

2 Answers 2

Reset to default
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

Improve this answer
3
  • 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, 2015 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, 2015 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, 2015 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

Improve this answer

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.