0

I have a SharePoint list for Doctors appointments, this list has two Date&Time columns with name Visit Starts and Visit Ends.

Now, I need to create a List view as Monday's Guests which should display only those items/appointments whose Visit Starts and Visit Ends date falls within Monday.

Similarly I have to create other views like Tuesday's Guests for dates falling within Tuesday and Wednesday's Guests for dates falling within Wednesday.

SharePoint List View, doesn't allow any formulas in the the filter, except [Today] and [Me]. Suggestions please!

0

So, you have to include saturday to tuesday appointments in your Monday calculation

IF( AND( WeekDay([Start Date])<=1 , WeekDay([End Date])>=1 )
  ,"Monday"
  ,"Week"
  )

You can replace "Week" to include the Tuesday appointment calculations

IF( AND( WeekDay([Start Date])<=1 , WeekDay([End Date])>=1 )
  ,"Monday"
  ,IF( AND( WeekDay([Start Date])<=2 , WeekDay([End Date])>=2 )
     ,"Tuesday"
     ,"Week"
     )
  )

Note that a saturday to wednesday appointment will always be listed as Monday. you will have to use Seperate calculated columns to also 'catch' Tuesday and Wednesday for that one appointment. Depends on your use case.

Update#1

This does not work for saturday-nnn because saturdaynr = 7

Can we make it as simple as: The First Workingday after the Start Date

=TEXT(  CHOOSE( WEEKDAY([Start Date]) ,2,3,4,5,6,2,2)  ,  "dddd" )
  • The above logic works fine, and it returns me the Monday from Saturday to Tuesday, but if I have Visit Starts Date as 6th of July which is Monday and Visit Ends on the same day, i,e. 6th of July. Then in this case also, it should show Monday, but now it is showing as "Week". – Shashikant K Jul 29 '15 at 11:41
  • @DannyEngelman I'm wondering how this will work if start date starts on a saturday, and end date ends on wednesday. Since you're checking if weekday is less than 1, it won't enter the if loop and never print Monday, right? – Akhoy Jul 29 '15 at 12:19
  • It should show monday as AND( 1<=1 , 1>=1) returns true. Make very sure your date entries are correct, check what WeekDay results for both your columns. – Danny '365CSI' Engelman Jul 29 '15 at 12:20
  • uberz91 is right.. I did all the calculations with sunday=1, saturday=7 – Danny '365CSI' Engelman Jul 29 '15 at 12:32
0

You can create a calculated column based on Start and End dates to get the day like so: =TEXT(WEEKDAY([StartDate]),"dddd"). You can then filter your view based on this column.

  • I have tried the same, I have created Calculated Columns say for Monday, and if I get Monday Date based on Start and End date, In list view what should filter criteria for this column? as in conditions, we cannot put any columns as equal to a "column name". – Shashikant K Jul 29 '15 at 9:49
  • In filter check if startCalculatedcol = "monday" and endcalculatedcol="monday". Is that not what you want? – Akhoy Jul 29 '15 at 9:56
  • I agree, but in a scenario where the items Visits starts date is on 4th of July which is Saturday and Visit Ends date is 7th July, which is Tuesday. Then again it should come under Monday's view, because from 4th to 7th there is a one Monday too. – Shashikant K Jul 29 '15 at 10:32
  • Ah I see, misunderstood your question. – Akhoy Jul 29 '15 at 11:27

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.