[Solved] Lookup if Date falls between 2 Dates

[nasch]

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

=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.

[FJCC]

Use a formula like

Code: Select all

=VLOOKUP(K4;$E$4:$G$8;3;1)

or

Code: Select all

=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.

[nasch]

Thank you very much, that worked great. I knew it was simple.