Was working on getting Tasks assigned to a user on a custom DataViewWebPart titled “My Critical Tasks”.  It is easy to obtain past due items, but sometimes you need both Past Due and due in X number of days.  I was battling (as with anything SharePoint) with this CAML query, but finally got this one to work. 

On my XSL I render an indicator image based on the date each Task has, looks something like this (FIGURE 1).  I enabled editing as well, so a user can edit the status of the task right from the home page and modify the Due Date, Status, and Notes if needed!

FIGURE 1 – Tasks shown based on the User that is currently logged onto the MOSS Site.



FIGURE 2 – Editing a Task on the WebPart

<Query>
  <Where>
   <And>
        <And>
             <And>
               <Geq>
                  <FieldRef Name='DueDate' />
                  <Value Type='DateTime'>
                     <Today OffsetDays='-21' />
                  </Value>
               </Geq>
                <Eq>
                   <FieldRef Name='AssignedTo' />
                   <Value Type='Integer'><UserID/></Value>
                </Eq>
             </And>
            <Neq><FieldRef Name='Status'/><Value Type='Text'>Completed</Value></Neq>
        </And>
      <Or>
        <IsNotNull>
            <FieldRef Name='Related_x0020_Event'/>
        </IsNotNull>
        <IsNotNull>
            <FieldRef Name='Related_x0020_Course'/>
        </IsNotNull>
     </Or>
  </And>
   </Where>
   <OrderBy>
      <FieldRef Name='DueDate' Ascending='True' />
   </OrderBy>
</Query>

Business Problem

One of my clients wanted to send email reminders to users one week prior to the project assigned tasks Due Date.

Solution

As you may or may not know, MOSS does not have that built-in functionality, but it is easily accomplished via a workflow created using the SharePoint Designer 2007.

Architecture

Calculated Column
I feel that working with complex date calculations (view my other post for a more date formulas  SharePoint List Calculated Column - Calculate Business Days) are best done on the SharePoint List side.  Therefore, I decided to have a calculated column that would already have the DateTime for when I need to send my Email Alert.

In this scenario it was fairly straight forward and so my formula simply looks like this:


This is what the column looks like when viewing the list items.  If you look closely, you will see that the column Days Prior To Due Date shows 3 days, but my email reminder column says the date to send my email is 6/21/2008.  The reason is that the formula on that column excludes weekend days!   

Workflow

The first Step on the workflow – Set Reminder Time

• I use the Add Time To Date Action, but don’t actually add time, I simply capture the value of the column TaskEmailReminderDate and store it on a variable called ReminderDate for use on my Step 2.
• Store the Task Title on a variable called SubjectTaskTitle, used when sending my email
• So that I can debug, I simply use the Log To History List Action to view the value of the ReminderDate variable

The Second Step on workflow – Send Task Reminder Email

There are no conditions, this step simply uses the Pause Until Date Action and uses the variable on Step 1 called ReminderDate to send out my email.



NOTE:
When I was testing my workflow, I was of course logged in as the “Administrator”, and so the workflow always failed to start automatically!  View this KB article for details.  My resolution was to simply create another account in AD and grant that account admin rights to my portal.  This prevented me from updating the Application Pool Identity for my Web Application.

Well I hope this helps you and let me know if it did!

Cheers,
Oscar

Ran into a nice little scenario where my client wanted to know how many business days were remaining prior to a Task Due Date. Once I had this date, I built a nice DataViewWebPart (see Figure 3) with the SharePoint Designer! This is what I came up with, hope it helps you!

A) Create a calculated column; call it something like "Days Prior to Due Date" in my case.

b) Paste this value into a Calculated Column, formula
=(DATEDIF(Today,[Due Date],"d"))-INT(DATEDIF(Today,[Due Date],"d")/7)*2-IF((WEEKDAY([Due Date])-WEEKDAY(Today))<0,2,0)+1

C) Make the output type "Single line of text"

NOTE: At the time of creating this calculated column, you must have another dummy column called "Today", it does not matter what data type it is. Once you create your calculated column, make sure to delete the Today column or your values will not work!

FIGURE 1 – Shows the formula for your calculated column to count only weekdays.

FIGURE 2 – Shows the sample Due Date and the calculated column output (far right)

FIGURE 3 – Shows you a dashboard with some XSL logic in the background to output an image on the left, and the days remaining calculated column value on the far right