# Identify the today's rate by searching between two given dates

• 0
• Question
• Updated 4 years ago

Hello,

I'm hoping for a simple way to generate the "Current rate" based on today's date from the example data table below:

Rate 1   0.195    Rate 1 start date    01-04-2016    Rate 1 end date    31-03-2017

Rate 2   0.193    Rate 2 start date    01-04-2015    Rate 2 end date    31-03-2016

Rate 3   0.188    Rate 3 start date    01-04-2014    Rate 3 end date    31-03-2015

Current rate ?

I guess I am really looking for a way to find how to search between two dates to get the appropriate rate.  However, the "AND" function doesn't seem to work as it would in Excel

If(TODAY() >= [Rate 1 start date] and <= [Rate 1 end date], [Rate 1]) and so on...

Any ideas would be greatly appreciated.

Thanks,

Donnie

• 0 Points

Posted 4 years ago

• 0

Ⲇanom the ultimate (Dan Diebolt), Champion

• 30,224 Points
If([Rate 1 start date] <= Today() and Today() < [Rate 1 end date], [Rate 1],
[Rate 2 start date] <= Today() and Today() < [Rate 2 end date], [Rate 2],
[Rate 3 start date] <= Today() and Today() < [Rate 3 end date], [Rate 3]
)

On second thought I don't this this works as I assumed all these fields were in one record.
• 0 Points
Perfecto!!  Thank you very much, Dandiebolt.  Greatly appreciated.  Makes sense now.