Tracking gift receipts

Stephanie Ulmer

I need to create a query or report that will help me determine if we are receipting our donors with a 48 hour window. Obviously I can use the gift date and receipt date, but I'm not sure how to get the two to "talk" to see if we are within the two days.



Anybody do this currently or can help?



Thanks,

Steph

Joshua Bekerman bCLO bCRE

There isn't a way in Query to compare the two fields (that I know of) so you'll probably have to do some work outside of RE (i.e. Excel, Access or a custom report with Crystal Reports).  If you use Excel I would set up a worksheet with two columns (gift date and reciept date), and a third column for the following formula:  =IF(NETWORKDAYS(A1,B1) > 2, "Took too long to send recpt", "Success!")



This will calculate the number of weekdays between the two and return a message if it was more than two days and another message if it was within two days.  Or, you can just use =(NETWORKDAYS(A1,B1)  if you just want to return the number of days.  Then copy the formula down to all rows necessary.  (If you want to get fancy, you could also have the formula exclude holidays when calculating.  Let me know if you'd like to know how.)



Then, every time you need an updated report, use the same worksheet and cut and paste the new data into the first two columns, and the formula should refresh on it's own after pasting the new data.



However, if you are familiar with Access or Crystal Reports I would recommend using one of those instead.

Jen Claudy

I do something similar, but as Josh suggested, I use MS Access.  I have to run a report of the Average number of days it takes us to acknowledge a gift (from the Gift Date that is the postmark to the date the letter is actually mailed which is on an Attribute).

John Heizer

In addition to the great suggestions already made one very crude way to get this (and it only works with some time frames, yours just happens to be one) is to create a Gift Query where Gift Date < Yesterday AND Gift Receipt = Not Receipted.  This won't give you a report on past performance, but you can use this as the basis for a Gift Query that can be used as a Dashboard to alert you to Gifts that haven't yet been receipted within your desired time frame.