Forum Discussion
Calculate a Fee if between a Date and on ProjID
Hello Experts,
I need to apply a rate to the excel table based on the following criteria:
1. if the [date] is between the rate table [Date From] and [Date To]
2. also on [ProjID]
The tricky part to me adding the [ProjID] criteria.
grateful for the help.
Let me know if there are any questions.
I have attached a sample file.
=INDEX(Table2[Rate],MATCH(1,([@ProjID]=Table2[ProjID])*([@Date]>=Table2[Date From])*([@Date]<=Table2[Date To]),0))*[@Amt]*(30/360)Maybe with this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.
7 Replies
=INDEX(Table2[Rate],MATCH(1,([@ProjID]=Table2[ProjID])*([@Date]>=Table2[Date From])*([@Date]<=Table2[Date To]),0))*[@Amt]*(30/360)Maybe with this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.
- Hi, I am not sure what is happening but it says thre is a best response give to Grahmfs13. I do not see that response though?
Nice. Very nice.
Could I possibly add a tweak to it?
If the date is outside of the rates table, meaning that the date in the raw data is >[Date To] then fall back to the rate that is the according to the next closest date? Right now, it will return a #N/A if the date is > the [Date To].thank you very much.
=IFERROR(INDEX(Table2[Rate],MATCH(1,([@ProjID]=Table2[ProjID])*([@Date]>=Table2[Date From])*([@Date]<=Table2[Date To]),0))*[@Amt]*(30/360),INDEX(Table2[Rate],MAX(IF(Table2[ProjID]=B2,ROW(Table2[ProjID]))-14))*[@Amt]*(30/360))Maybe with this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.
