0

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

0

You mean you want a Calculated Column Formula to correctly count the days

  • Thank you. Unfortunately those don't solve the problem. If there were a way to update automatically that field, then the formula could stay in a calculated column and a second could compare it with the week indicator and a filter could be set to only show when the comparison column = 1 (true). – Napoleone's Master Jun 27 '16 at 15:29
  • Yes.. if it could...but it can't .. so then your only option is to write server-side code that does what you want – Danny '365CSI' Engelman Jun 27 '16 at 16:01

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.