The first step to batch updating the status for a set of volunteer opportunities and occurrences, is building the report to get us the necessary information to update the record.

For this example, we will create a new Report

Go to Reports and click on the Create New Custom Report button

Once you are able to edit the report, make the following changes:

1) Show:  All Occurrences

2) Date Field: End Date & Time

3) Range: All Time

4) Columns to Add

               Volunteer Opportunity: Record ID

               Occurrence ID (Salesforce.com ID field)

               Volunteer Opportunity: End Date   (Not the Volunteer Opportunity:End Date & Time)

               Occurrence End Date & Time

5) Since it can be challenging to decipher the Salesforce.com IDs, you can keep the additional columns such as Volunteer Opportunity Name to help you in managing the data.   If you need to filter or sort the data, you can also include the Occurrence: Occurrence ID field (OC-######).

Once all the columns you need are in place, click on Run Report and then Export as .csv

Open the Exported table and save a copy as a back-up of the data.

This is where it gets tricky.

What we need to do is compare the end date of the occurrence with the end date of the volunteer opportunity, but the end date of the occurrence is in date & Time format so we first need to separate the date and time into different cells.  This is done by:

1) Highlighting the column where the end date & time for the occurrence is listed.   It is important that the End Date & Time be the furthest column to the right; otherwise, it'll overwrite the date when it is moved into the other columns.

2) Then select the option for "Text to Columns" usually under the Data menu.

At the bottom of the report, there is a footer, make sure to Delete these five rows from your table prior to importing the data.

Once all the changes have been made, Save the file in a location where you can find it for the import.

In the Text to Column Wizard that opens, choose the following:

Step 1- Select Delimited.   Then click next

Step 2- Select "Space" under the Delimiters options.  Then click next.

Step 3-  Confirm that the text is going to be split correctly. Then click Finish.

Once the Date and Time has been split into separate columns, we need to change the format of the date column (Column E) so that it matches the Volunteer Opportunity End Date (Column C).

To do this, highlight Column E and select the cell format mm/dd/yyyy.  (The cell format option is located in different places for different, programs.  You should look under the Edit or Formatting menus. Some programs allow you to right click (or sometimes called wrong click) to get a limited menu of options and Format Cells is usually included).

Now that the cells are formatted the same way, we can enter a formula to compare the values.

In Column H, Row 2 (or another empty column, as long as you are in row 2) enter the formula =IF(E2>C2,TRUE,FALSE).  

Where E2- represents the cell that has the occurrence end date, and

Where C2- represents the cell that has the Volunteer Opportunity End Date.

(Note:  if E2 and C2 are not the right locations of that information, then please update the formula so that it has the correct location for each of those values.)

Once the formula is correct, copy the formula into the remaining cells in the table.

What the formula will return is the rows that are marked TRUE, are where the Occurrence End Date is past the Volunteer Opportunity End Date so those Volunteer Opportunity End Dates need to be updated so that all the occurrences fall within the Volunteer Opportunity Date range.

Once you have copied the formula into all the rows.  Any rows, where the result was FALSE can be removed from your table.  

At this point there are a few different options.

1) If there are only a few Volunteer Opportunities in which the Occurrence End Date extends beyond the Volunteer Opportunity End Date you can just use the resulting table as a reference and go into the actual Volunteer Opportunity  records and change the End Dates to be inclusive of the Occurrences.

2)  If there are a number of these records that are impacted by this, then you can perform a Import Custom Objects update.

To finish preparing the table to update the Volunteer Opportunity End Date, you need to:

a) remove any duplicates for the Volunteer Opportunities.  There might be more than one occurrence that is outside of the Volunteer Opportunity Date range, so make sure that you use the occurrence that is the furthest out to set the new Volunteer Opportunity End Date.

b) Update the date in the Volunteer Opportunity End Date column to match (or I recommend 1 day greater than) the Occurrence End Date.

(Excel Tip:  you can enter the formula =E2+1 into the C2 cell and it will calculate a new date for you that is one day greater than the Occurrence End Date)

c) When using the Import Custom Objects in Salesforce, the only two columns that you need to map are:

               Volunteer Opportunity: Record ID to Salesforce.com ID

               Volunteer Opportunity: End Date to End Date.

d) make sure you have deleted the report footer if you hadn't already.

For directions on how to perform a Import Custom Object, click here