Hello
I am hoping someone can tackle this challenge with what I am looking to achieve.
In the spreadsheet, Columns A-C, I have a report that comes in with data.
In this case, what I am looking to do is get the value for Column E (end date) based on the two tables and based on the data that comes in.
This is all date based and returns the value which is also the a date.
I am only concerned with Client Sally. So IF Sally appears in Column A, the formula would look at the Account Code and the transaction date and the closing date
(in some cases) to determine the End date.
The tables are broken up by Account Code
The start and end date ranges are the same for both codes. But the calculation is a bit different per code.
If the Client is Sally and the Code has EOM4 or EOM5 etc.in Column C, formula would look at the transaction date (column B) and the Account Code (column C) and return the applicable date from
the EOM table - Columns K-P. Row 8 and Row 9 are examples of this code. For this EOM code, the date returned is always the 30th of the month applicable or the last
day in February.
If the client is Sally and the code is "InSeason" in column C, then the formula would also need to also look at the closing date. If both the transaction and closing dates
fall within the Start and End date- Column R and S, then the value returned should be the first date, Column T, (row 3 as an example). but if the transaction date is
within the range (Column R and S) and the closing date is after the date in Column T for transaction date (Row 4 example), then the value returned would be from Column U, which is
essentially 45 days later. For this code, the value returned will either always be the
15th of the month, or the 30th of the following month except for February which would be the last day of the month.
Clear as mud?
SallyCodeProblem.JPG
Bookmarks