[Solved] Lookup if Date falls between 2 Dates

[Solved] Lookup if Date falls between 2 Dates

I'm in the process of converting a spreadsheet originally made in Excel into an OpenOffice-friendly version. The multitude of lookups and other formulas do not cross-over well and unfortunately go beyond changing , to ;.

One spot I have not become stuck in however is looking up if a date is within two dates. I have one lookup table which includes a "table-ized" fiscal calendar, and the other has data entered to it on a daily basis. In the data-entry table, I need a column to detect what fiscal week a line-item entry falls into and return a given identifier for it. In Excel, the formula was:

Code: Select all   Expand viewCollapse view
`=IFERROR(LOOKUP(D2,ddWeeks[Week Starting]:ddWeeks[Week Ending],ddWeeks[Week Ending]),"")`

Where D2 was the line-item date and ddWeeks was the table which included the fiscal calendar. I know how to work around IFERROR in OpenOffice, however I cannot determine how to reference the date falling between the two dates.

I've attached an example to illustrate what I'm referring to, where O2:O4 should return "FY2019-P1-W1". Any advice is appreciated.
Last edited by robleyd on Tue Mar 12, 2019 11:37 pm, edited 1 time in total.
Reason: Tagged [Solved] [robleyd, Moderator]
OpenOffice 4.1.5 // Windows 10 Enterprise
or
OpenOffice 3.4.0 // Windows 7 Pro
nasch

Posts: 7
Joined: Tue Mar 12, 2019 2:39 pm

Re: Lookup if Date falls between 2 Dates

Use a formula like
Code: Select all   Expand viewCollapse view
`=VLOOKUP(K4;\$E\$4:\$G\$8;3;1)`

or
Code: Select all   Expand viewCollapse view
`=LOOKUP(K4;\$E\$4:\$E\$8;\$G\$4:\$G\$8)`

These find the row where the value in the E column is the last one that is less than or equal to the value in K4. That is, they scan down the (ordered) E column until they find a value greater than K4 and return the value in G from the previous row.
Windows 10 and Linux Mint, since 2017
FJCC
Moderator

Posts: 7607
Joined: Sat Nov 08, 2008 8:08 pm

Re: Lookup if Date falls between 2 Dates

Thank you very much, that worked great. I knew it was simple.
OpenOffice 4.1.5 // Windows 10 Enterprise
or
OpenOffice 3.4.0 // Windows 7 Pro
nasch

Posts: 7
Joined: Tue Mar 12, 2019 2:39 pm