Sorry, there's a lot of text here...
I have an employee list in a basic list webpart that matches names with job duties. The duties change each week with four weeks in each full cycle (week 1, 2, 3, 4, 1, 2, etc.). To indicate the week number each row of names and duties has a column with a number (1-4) indicating for which week that employee-duty combination applies. For example:
- Joan Garbage 1
- Steve Raking 1
- Mike Watering 1
- Carol Weeding 1
- Joan Raking 2
- Steve Weeding 2
- Mike Garbage 2
- Carol Watering 2
... and so on
I have constructed a formula that in principle could tell me which week in the rotation we are on every day based on a start date (in this case, Sunday 6/5/2016):
(1+(ROUNDDOWN(DATEDIF("6/5/2016",[TODAY],"D")/7,0)))-(4*(ROUNDDOWN(1+(ROUNDDOWN(DATEDIF("6/5/2016",[TODAY],"D")/7,0))/4,0)))
I don't have the formula in front of me, so I apologize if the syntax is a little off. Anyway, if the TODAY field here were updated automatically, this formula would return the number of the week in the four-week rotation which could be matched with the week number from the third column and filtered to only show that week.
However, I am not sure how to update this automatically except I know that the [Today] value in filtered views updates with the current date on each view of the list. Trying to create a column named TODAY with the date set to default as Today doesn't seem to work. Granted, the formula returns valid values, but they are only applicable if I manually update that TODAY column, and changing each one every week defeats the purpose of this being automatic (not to mention if it must be done by hand there are easier ways to do it).
So the formula works, it's just a matter of how to always get the current date run through it. I'd like it to be an automatic filter, but all I see for examples using filters are very simplistic date calculations/comparisons. And when I enter this formula in the filter value for showing items only when Week "is equal to" (formula above), I get an error as it seems too complicated for little SharePoint to figure out.
And so I'm stuck wondering if there's a way to to this or not. Is there?
Thank you.
NM
