Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

I've been working on this issue forever now...Here's what I'm trying to do. I have a custom list where people add some info. I crated 2 views to view items added today and yesterday. Now I'm trying to figure out how to create a view that will always show items added last friday no matter what day of the week I look at the view... I tried following and it didn't work for me:

  • Filtered "Created" column where it =[Today]-3, which didn't work because I can use it only on Monday
  • Filtered "Created" column and used following formula:

IF(WEEKDAY([Created],2)=1,[Created]-3,IF(WEEKDAY([Created],2)=2,[Created]-4,IF(WEEKDAY([Created],2)=3,[Created]-5,IF(WEEKDAY([Created],2)=4,[Created]-6,IF(WEEKDAY([Created],2)=5,[Created]-7,IF(WEEKDAY([Created],2)=6,[Created]-1,[Created]-2))))))

this formula always gave me "Filter Value is not in supported date format", so i tried using "text(above formula,"mm/dd/yyyy") and it didn't work either

  • Because of the format I decided to create an extra column that simply converted "Created" into text...so =Text([Created],"mm/dd/yyyy"). And then I tried to filter newly created column with the above formula...it didn't work...
  • I tried using another formula in the filter, and got another date format error...However, I like this formula the best as it pretty much checks for day for the week, and if it's not Friday, than does calculation...

    =IF(OR([Today]=1,[Today]=2,[Today]=3,[Today]=4,[Today]=6,[Today]=7),[Today]+(5-[Today])-7,[Today]-6)

I'm looking for some ideas or suggestions to try. Again the goal is to be able to pull last friday's data any day of the week

Thank you in advance for your assistance and ideas.

share|improve this question

2 Answers 2

Unfortunately the filter doesn't support any advanced formulars. The only formulars are today +/- some days.

You may solve your problem by creating a calculated column CreatedWeekday as WEEKDAY([Created],2)

Then on you view you can use the filter:
CreatedWeekday Equals 5
AND
Created Greater than [Today]-8
AND
Created Less than [Today]

share|improve this answer
    
best solution so far...Not ideal, considering that I have couple more filters to play with, but better than nothing! –  user1718149 Oct 8 '12 at 18:37

Maybe this can point you in the right direction:

http://www.petestilgoe.com/tag/calculated-column/

http://www.myriadtech.com.au/blog/Alana/Lists/Posts/Post.aspx?ID=8

I would go with creating a calculated column like so:

Week end (Friday)

=[Created]+(5–WEEKDAY([due date],2))

then subtract 7 in the filter

share|improve this answer
    
It doesn't work: when I create a column with the above formula it calculates just fine. But when I add a filter for "Created" column with the formula [New Column]-7 I either get an error about wrong date format OR if i tried to convert the column to date there's no values returned what so ever....Any other suggestions? please? –  user1718149 Oct 4 '12 at 20:04
    
I also trieed following formula: IF(WEEKNUM([Today])-WEEKNUM([Created])=1,IF(WEEKDAY([Created],2)=6,"Last Friday","hide"),"hide"). It doesn't look like Sharepoint likes WEEKNUM –  user1718149 Oct 4 '12 at 20:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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