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

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

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

Photo of Donnie

Donnie

  • 0 Points

Posted 4 years ago

  • 0
  • 1
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,004 Points 20k badge 2x thumb
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.
Photo of Donnie

Donnie

  • 0 Points
Perfecto!!  Thank you very much, Dandiebolt.  Greatly appreciated.  Makes sense now.