Match dates in different formats and input amount in correct field

[cokesm]

I need a formula to place an amount in the correct column by doing a date match.

I have one column called "invoice date" in the format dd-mm-yyyy (ie 3-May-2011), and a column called "amount". then, across a series of columns,I have mmm-yy (May-2011, Jun-2011 etc etc). I need the formula to match the date in the "invoice date" column to its corresponding month-year date across the top and if there is a match, populate the cell with the amount. This is for revenue forecasting.


Thanks

 

[cmissal]

Assuming that both dates are in structured date fields this should work... if not you will need to first convert one/both to date before doing the compare.


Replace the text in [] with your cell references.


=IF(AND(YEAR([DATE1CELL])=YEAR([DATE2CELL]),MONTH([DATE1CELL])=MONTH([DATE2CELL])),[AMOUNTCELL],"")


Hope that works for you.


PS. Someone else may have a better approach...

 

[cokesm]

that worked! thank you