Committee: Gift Aid

From U3A Support
Jump to navigation Jump to search

If joining members are a UK Taxpayer, they may, if they so wish, opt in to treat all subscriptions and donations they make as Gift Aid. If this is the case, then we are able to reclaim some tax accordingly. Members consent to this by ticking a box on the application form when they join or later by editing their profile.

The Treasurer is responsible for forwarding the "Gift Aid List" to HMRC at the appropriate time. The report is available to the Treasure from Committee | Manage | Manage Members.

Committee The Gift Aid dialog box

By default, the dates are from one year ago to today although you can change them to suit your needs.

The report is downloaded to your local computer in the form of an Excel spreadsheet. For data privacy and better security, the spreadsheet should be deleted from your local computer when you have finished with it.

Data fields required are as such:

Giftaid Spreadsheet

You can see that Col F, the Donation date is left aligned and in the format yyyy-mm-dd. This is not a date field (date fields by default are right aligned) and not in the format that HMRC require, they require dd/mm/yyyy. For whatever reason, the field "Donation Date" has been exported as a text field. (At some point in the future we will get this changed, but for now we will have to do an adjustment). The process is as follows:

In a new column, we use the Excel "DATEVALUE" function to convert the text field to a date field. Theses will be calculated values referenced to the original text field from Col F.

We then take these calculated results and paste them to another new column as just a value, ie a number that is not related to any other cells. After that we format the column to be a date field with the data type as dd/mm/yyyy.

Finally for the adjustment we can then delete the two unwanted columns. Col H and F.

Specifically, the steps to take are detailed below.

Insert two new columns after col F.

Insert two new columns

Not shown here, but check that the formatting for the new Col H is "General" (Right click the column header, then Format Cells and General). Then in cell H2 enter the formula "=DATEVALUE(F2)"

Enter the Formula for DATEVALUE

and press enter. H2 is now a date field, albeit just a serial number from the 'date' from col F2.

Converting to a date field

Now select H2 again and from the bottom right hand corner of the cell click and drag to that last row of the spreadsheet to copy the formula into rows H3 to the end (bottom of the spreadsheet).

Converting to a date field

While the whole column is still selected, copy that data to you clipboard (either Ctrl + C, or from the Ribbon menu Home | Clipboard section

Converting to a date field

and right click in Cell G2 to give you your paste options. You want the 2nd icon to paste the actual value (rather than a reference).

Converting to a date field

Col G is then populated with actual real date values, although at this point it is a date serial number.

Converting to a date field

Right Click the column header for Col G and then select "Format Cells...."

Converting to a date field

to change the category to a Date and the Type to be the format dd/mm/yy. Click OK to finish.

Converting to a date field

Your Col G is now what you need. Compare the data with Col F to make sure it is all correct. Then you can write the column header in Cell G1 (Donation Date) and delete columns H and F. Adjustment done!

Converting to a date field

See the Microsoft support article for a full explanation of the DATEVALUE function.

The final part is to then present the data in the file format that HMRC require.

HMRC require the data in .ods format (Open Document Spreadsheet). You can do this from Excel: Go File | Save as | and then select the type as Open Document Spreadsheet (right at the bottom of the list).

Saving an excel spreadsheet as an ods file

To upload the file to HMRC you must have an HRMC Government gateway account. If the treasurer changes, the new incumbent will need to complete and CHV1 (Charities Variation form) which allows the new claimant (the new treasurer) to change the details on the HMRC Charity reference.

From the database

This is a bit of a faff to check. You can check the raw data from SQL files with this expression:

SELECT `title`,`forename`,`renew`,`surname`,`house`,`postcode`,`date_paid`,`amount`

FROM `u3a_members`, `u3a_subscriptions`

WHERE u3a_subscriptions.members_id =

AND u3a_members.status = "Current"

AND u3a_subscriptions.date_paid > '2022-07-04' ||Check this date first if you are doing it for real||

AND u3a_members.gift_aid is not null

ORDER BY u3a_members.surname ASC;

Good luck!

Navigate to the previous or next article