Hello all,
Hope you can assist with a slight problem I've encountered.
I have a large column of date entries (several columns, actually, but same applies to all of them).
This column is a Contact Date (and has been named so it can be accessed by reference as 'contact'), recording when we first interacted with an individual. It contains values up to ten or so years ago.
There is a report associated with this data, where I need to break down, by month, when people contacted us. This information is only relevant however to people contacting us in the current financial year.
At the moment, I have to take the date of contact, pass it through an AND to see if it falls into the correct year. This obviously leads to a column of TRUE/FALSEs 1-to-1 related to the contact dates. If it is TRUE, then I take the Month() value from the contact date. Using a Countif across the column of determined months is used in the report (for example,
=countif(contactResults,3)
returns the value for people first seen in March).
I was thinking a VBA script would be able to compress this to a single function, except I do not know how to address a column for input purposes. I'm aiming for something akin to the following:
Function newContacts(inputSet As [?], selectedMonth As Integer)
newContacts=0
Do
if ((inputSet[element]>=(1st date of fin.year)) AND
(inputSet[element]<=(last date of fin.year)))
then
if month(inputSet[element]) = selectedMonth
then
newContacts = newContacts +1
end if
end if
Loop Until [end of inputSet]
End Function
such that a call to newContacts(contact,3) should return the same as the nested functions described above. Alternatively, referring into the function by newContacts(N:N,3) would work well enough (although be less clear to other users).
Is this possible?
Many thanks in advance.
Bookmarks