Lifetime Hours as of Last Year for Pre-v5.9

In version 5.9 you can get lifetime hours as of the date you set in the Years of Service Report. For previous versions the Lifetime Hours on the Years of Service report calculates as of today. No matter how you set the date in the Years of Service report, or if you change the computer's date, it brings up the number of hours in the Lifetime Hours field in the volunteer's record.

The easiest way to get lifetime hours for last year for versions before 5.9 is to run the Years of Service report right after the December hours are posted. This works fine if you post hours manually, but will be foiled for organizations which use the Web Assistant and/or Touch-Screen Assistant, because new hours enter automatically, increasing the volunteers' lifetime hours. Lifetime hours in the Reporter always calculate as the day the report is run.

The goal is to get lifetime hours as of a specific date, so you sometimes have to subtract the accumulated up to the date of interest from the Lifetimes Hours, which always run as-of-today. This can be done with a formula in Excel, using the Hours Served Spreadsheet from the Volunteer Reporter.

This example is done in Office 2003.

To get the lifetime hours as of last year, first run this spreadsheet and get the two hours columns side by side. This is an overview, details follow.

Run the Summary by Volunteer Spreadsheet

The Hours Summary by Volunteer menu

The Hours Summary by Volunteer dialog box

How to Delete Extra Columns in Excel

Delete extra columns, e.g., cols D-AZ in this example.

Alternate Way to Select and Delete Multiple Columns

Here is an easy way to delete several columns at a time. Highlight one column on its letter heading and hold down the left mouse button, dragging as far right as you want. Let go. Right-click in the highlighted section and choose Delete from the menu to delete the whole block at once.

How to delete five columns at a time.

Add a Column

Insert a column

Insert a column

The Subtraction Formula to Subtract This Year's Hours

Type the formula in the empty cell to the right

Expand the Formula to the Whole Column

Point the cursor to bottom Right corner in E4 [the formula cell - cursor changes to thin cross], click and hold, dragging to the last cell E19.

Drag from the first to the last cell

The formula is propagated to all the data cells in that column

The formula is propagated to all the data cells in that column

Sort on the 'Up to 2008' Column

Click on Data menu

Expand the selection

Expand the selection

Lifetime Hours as of last year, in ascending order

The Up to 2008 hours above are sorted in ascending order, with the lowest number on top. This column presents the Lifetime Hours as of last year. You can pick out the volunteers with the numbers relevant to your award levels.

Revised 03/Aug/2011

Back to the Home Page.
Back to the Home Page.