NEWBIE QUESTION: How do I add up volunteer time & add up the time by person from a form?


#1

Hello! I am super new to understanding Airtable & I am having trouble finding how it can work for a nonprofit. I am attempting to create a volunteer time tracker. Our current system is through volunteers submitting a google form that then goes into a google sheet that will tabulate up all of the total time volunteered (from the start & end time of their shift). There are then columns in this sheet that find the total hours volunteered in total in the office.
I want to do something like this with Airtable. I have made a sheet that asks for all of the correct information. However, I am having a hard time figuring out how to 1. tabulate the hours/minutes of each person (like by the name entry), 2. the total hours/minutes entered in all & 3. by volunteer type (this is also field/question on the airtable form I made).
Please let me know how I could implement this kind of format on Airtable. Thanks in advance!


#2

You need two tables in one base: Table 1: Volunteers and Table 2: Time Log. Set your tables up something like the following:

[Time Log] Table:

  • Link to Another Record field: {Volunteer} >> links to your [Volunteers] table
  • Date field 1: {Start Time} >> include both the date and time
  • Date field 2: {End Time} >> include both the date and time
  • Formula Field: {Duration} >> use the formula DATETIME_DIFF({End Time},{Start Time},"minutes"). If you want this in decimal hours add / 60 to the end of that formula

Make a form view for the [Time Log] table with all those fields included except for {Duration}, which is automatically calculated by formula and thus doesn’t need user input

[Volunteers] Table:

  • Single Line Text field: {Name} >> type the name of each volunteer
  • Link to Another Record field: {Log} >> links to your [Time Log] table. Should automatically appear if you already made the Link field in the [Time Log] table
  • Rollup field: {Total Hours} >> point to the {Duration} field in the [Time Log] table using the SUM(values) aggregation. This field will show you the total number hours that particular volunteer has ever logged.
  • Single Select field: {Type} >> add your options for the volunteer type categories.

Then you can group records in the [Volunteers] table to view the total sum of hours volunteered for each volunteer, and by volunteer type using the summary bar.


#3

for clarification, if the volunteer type is not a trait of the individual (i.e. regular volunteer, seasonal volunteer) and instead changes per event (i.e. church event, school event, work event), you will need the {Type} field in the [Time Log] table.