[Solved] Lookup if Date falls between 2 Dates

Discuss the spreadsheet application

[Solved] Lookup if Date falls between 2 Dates

Postby nasch » Tue Mar 12, 2019 2:57 pm

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

Postby FJCC » Tue Mar 12, 2019 3:15 pm

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
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7314
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Lookup if Date falls between 2 Dates

Postby nasch » Tue Mar 12, 2019 4:17 pm

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


Return to Calc

Who is online

Users browsing this forum: Epartsman and 33 guests