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!

Improve this question

2 Answers 2

Reset to default
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" )
Improve this answer
4
  • 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 Oberoi
    Commented Jul 29, 2015 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
    Commented Jul 29, 2015 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
    Commented Jul 29, 2015 at 12:20
  • uberz91 is right.. I did all the calculations with sunday=1, saturday=7
    – Danny '365CSI' Engelman
    Commented Jul 29, 2015 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.

Improve this answer
4
  • 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 Oberoi
    Commented Jul 29, 2015 at 9:49
  • In filter check if startCalculatedcol = "monday" and endcalculatedcol="monday". Is that not what you want?
    – Akhoy
    Commented Jul 29, 2015 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 Oberoi
    Commented Jul 29, 2015 at 10:32
  • Ah I see, misunderstood your question.
    – Akhoy
    Commented Jul 29, 2015 at 11:27

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.