1

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.

Improve this question

2 Answers 2

Reset to default
1

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]

Improve this answer
1
  • best solution so far...Not ideal, considering that I have couple more filters to play with, but better than nothing!
    – user1718149
    Commented Oct 8, 2012 at 18:37
0

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

Improve this answer
2
  • 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
    Commented Oct 4, 2012 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
    Commented Oct 4, 2012 at 20:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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