Hi all,
I'm relatively new to VBA, but have been searching the help forums for awhile now with no luck. I'm hoping you can help me with this...
I have a series of vehicle projects with starting and ending reporting dates for each. It looks something like this:
Project # Vehicle # Starting Date Ending Date
2010-001-TEST 1 2011 Q2 2016 Q4
2010-002-TRUE 1 2010 Q3 2015 Q2
2010-001-TEST 2 2013 Q1 2017 Q1
I need a function that will find the earliest starting date and last ending date for each project overall, which in this example would be:
2010-001-TEST 2011 Q2 2017 Q1
2010-002-TRUE 2010 Q3 2015 Q2
Something akin to a modified version of the SMALL and LARGE functions with a logical test incorporated would do it I think. Alternatively, I've already got a user-defined function called Quar2Num that can translate the starting and ending dates into numbers, but I can't get the results to feed into another function properly to find the smallest or biggest numbers. Here's the function and how I'd want to use it:
Function Quar2Num(yearquarter As Range)
For Each cell In yearquarter
Quar2Num = Int(Left(cell, 4)) + (CDbl(Right(cell, 1)) - 1) / 4
Next cell
End Function
= MIN(Quar2Num($C$3:$C$6) = 2011.25 (2011 Q2)
Can anyone point me in the right direction?
Thanks!
Christine
Bookmarks