SharePoint 2010 Views / Date Comparison Problem

0
votes

I'm using Sharepoint 2010 Enterprise and I'm trying to create a view which seems like it should be pretty simple. It is based on the task list:

  • Task Name
  • Start Date
  • Due Date

Calculated Columns

  • Start_Due_Month_Year =MONTH([Due Date])&"/1/"&YEAR([Due Date]) e.g. 7/1/2011
  • End_Due_Month_Year =MONTH([Due Date])&"/"&CHOOSE(MONTH([Due Date]),"31","28","31","30","31","30","31","31","30","31","30","31")&"/"&YEAR([Due Date]) e.g. 7/31/2011
  • Start_Start_Month_Year - same as above
  • End_Start_Month_Year - same as above

The calculated columns are configured to return a date & time type, formatted as date only. Everything appears to work perfectly in the All Items view.

The problem is if I try to create a view where either the START DATE or the DUE DATE appear in the same current calendar month. So, if I had a task started 7/10/2011, it would appear and if I had a task started 6/15/2011 but due 7/15/2011 it would also appear.

So, I'm just using a series of filters to say if Start_Due_Month_Year <= [Today] AND End_Due_Month_Year >= [Today] - this is the simple case of if the due date falls within the current calendar month.

This view returns no results. I tried manually converting the date column via the DATEVALUE function as well with no luck.

Any ideas?

Thanks!

1
datesharepoint-2010viewscalculated-columns

1 Answers

0
votes

Solved this - there were a couple issues:

  • DATEVALUE function must be used in the calculation even though the return type was date for the comparison on [Today] to work.
  • To get both sets of dates to work, I had to use an intermediate column which would always be true (such as ID > 0) to simulate the parenthesis. So what I wanted was (SS && ES) || (SD && ED) - what SharePoint was giving me was SS && ES || SD && ED...not quite the same.
  • Finally, it mattered if I used the Start Date as the first item in the clause rather than the Due date. Even though reordering them shouldn't matter logically, the start date column HAD to come before the Due date column for the view to return all results.