IF Function with Date revised question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

The previous answer from Michael was great. However, I realized that I will
have more than 7 nested If statements.
I didn't need the year so, I took it out of the equation.

Is there a way to streamline the formula for 12 months?

the previous answer =if(and(month(d1)=1,B10,if(and(month(d1)=2,C10,0))

cell: D1 will change from January to December (each months return)
Cells: B10, C10, E10, D10, etc for 12 months - contains prepayment amounts.

As the months change I need the formula to look at the current date and
return the previous months prepayment amount.
There is a formula from the current return that fills this schedule on a
monthly basis.

The prepayment schedule looks like this:

location dec jan feb etc
1 boca raton 5,000 6,000 7,000
2 St John 2,000 1,000 7,000
etc.

The formula will be individual for each location return.
Should I use a lookup and range name the location and date?

Any suggestions?
 
Hi Ken,

Thank you for the reply, its nice and short, however can you shed a little
more information on the formula? I tried this and I came up with a "value".

The month, payments and the cell that will hold the formual are all on
difference worksheets in a workbook. Do they need to be next to each other?

I assume that the range( $A$10) that will return the payment amount should
also include the headings for prior month date?

I'm not exactly sure how the data must be positioned to get the desired
results.

I'm sure this will work for me I just need to set it up properly. Can you
give me of an example of this formula?

Thank you so much for your help.

Debbie
 
Probably my fault, I just reread your message and am wondering if the data
you have in D1 is actually a real date or not. I'm guessing now that it
isn't.

What the OFFSET formula does in this instance is to take a starting point,
and then move away from that cell by x number of columns. The x is
determined by the Month number,eg

If D1 = Jan, then Month(D1)=1, likewise if D1 = Feb, then Month(D1)=2 etc,
AS LONG as the value in D1 is a valid date

D1=Jan= 1 ==> =OFFSET($A$10,,MONTH(D1)) = B10
D1=Feb= 2 ==> =OFFSET($A$10,,MONTH(D1)) = C10
D1=Mar= 3 ==> =OFFSET($A$10,,MONTH(D1)) = D10
D1=Apr= 4 ==> =OFFSET($A$10,,MONTH(D1)) = E10
D1=May=5 ==> =OFFSET($A$10,,MONTH(D1)) = F10

If it is not a real date, then assuming it is a 3 letter abbreviation in D1
representing the Month, then you can try:-

=OFFSET($A$10,,MATCH(D1,{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Se
p","Oct","Nov","Dec"},0))

or if you really want the full month then adjust the formula above, or you
can try

=OFFSET($A$10,,MATCH(LEFT(D1,3),{"Jan","Feb","Mar","Apr","May","Jun","Jul","
Aug","Sep","Oct","Nov","Dec"},0))
 
Thank you Ken,

I did a test with the example ranges and now I understand how it works.

This will help tremendously, in my job.

Sincere thanks, Have a Great Day!
 
Back
Top