Date: prev next · Thread: first prev next last
2013 Archives by date, by thread · List index


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+unsubscribe@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

Context


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.