I'm trying to pull the exchange rate for a given date, but we only enter exchange rates once a week so there is not always an exact match. Is there an easy way to modify the formula below to have it look up the date or the first date prior to it in the Currency Exchange Rate table? Appreciate any advice on this. Bob T
nl(,"Currency Exchange Rate","Exchange Rate Amount","Starting Date",$O66,"Currency Code",$D66,"LCY Code","USD")
O66 = 29-Jun-14
D66 = EUR
sample of the Currency Exchange Rate Table data
Currency Code Starting Date Exchange Rate Amount Adjustment Exch. Rate Amount Relational Currency Code Relational Exch. Rate Amount Fix Exchange Rate Amount Relational Adjmt Exch Rate Amt LCY Code Budget Exchange Rate Amount Relational Budget Exch Rate Amt
EUR 06/02/2014 0.734376 0.734376 1.0 Currency 1.0 USD 0.7634 1.0
EUR 06/09/2014 0.734538 0.734538 1.0 Currency 1.0 USD 0.7634 1.0
EUR 06/16/2014 0.738825 0.738825 1.0 Currency 1.0 USD 0.7634 1.0
EUR 06/23/2014 0.737518 0.737518 1.0 Currency 1.0 USD 0.7634 1.0
EUR 06/30/2014 0.73443 0.73443 1.0 Currency 1.0 USD 0.7634 1.0
Date
Votes
2 comments
-
Jet Reports Historic Posts Hi BT,
If you always enter the exchange rate on the same day of the week (i.e. you always do it on Monday), you can use an Excel formula such as =E7-WEEKDAY(E7,3) where E7 is a date.
That function will always return the date of the Monday for that given week no matter what date you enter. I've also attached the example.
If there is no consistency to when the rate is enter (i.e. sometimes it is Monday, sometimes it is Wednesday), you could use a similar technique to create an NL(Filter) to pull the exchange rate for a date range. If there is only one day within that range with the exchange rate, it will find it. -
Jet Reports Historic Posts Thanks! I added a column for date (E14) minus 6 to get (E13) the range within the past week and the formula =NP("datefilter",$E$13,$E$14) works great. Thanks again for the multiple suggestions.
Please sign in to leave a comment.