On 09/14/2013 07:01 PM, Tim Deaton wrote:
The closest I came to a solution was a scenario where all the dates
for the year were in one column.
I put the dates for the year (Monday 12/31/2012 thru Sunday 1/5/2014
in A5:A375.
Then, in A1 I put whatever date I'm looking for.
Off to the side (D1) I entered: =VLOOKUP($A$1,$A$5:$A$375,1,0)-(A5-1)+4
VLOOKUP(A1,$A$5:$A$375,1,1) finds the date you're looking for.
-(A5-1) subtracts the day before the first date on the list FROM
the date found.
+4 adds in the four rows above the range.
The result is the row number of the date you're looking for.
A simpler formula: =MATCH($a$1,$a$5:$a$375,0)+row($a$5)-1
MATCH returns the offset into the array (so if $a$1 = $a$5, MATCH would
return 1), and row($a$5)-1 adjusts it to give the absolute row.
The reason I do it this way is that if you name the array (e.g. name it
YearRange), it works as follows:
=MATCH($a$1,YearRange,0)+row(YearRange)-1
So you don't need to know what row your array starts on to write the
formula.
Jim
--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
