Excel 2007 : Excel date formula (tricky)

[priceless07]

I am facing a typical problem for which i need a workaround in Excel. Our financial system (legacy software) gives us a report of our total payment out standings based on the invoices generated on daily basis. the same is divided as 0-30 days, 31-60 days etc. I also manage the same in Excel to generate reports and charts for management purposes. The system is designed in such a way that even if the invoice falls within 0-30 days, if the month changes it moves to the next age group i.e. 31-60 days.
This does not happen for other age groups only for the 0-30 days age group.

E.g. inv no. 2341 generated on 20-June 2010 shows in the 0-30 age group till 30th June 2010; If I take the report on July 1st I see it moved to the 31-60 days category. I want the same thing to be replicated in Excel. Currently I am using this formula to check on the age of the invoice and move it to the various age groups automatically [=IF(AND($L257<=30),$F257*1,"0")] /// [=IF(AND($L257<=60,$L257>30),$F257*1,"0")] .... Where L is the column where I calculate the age of invoice in days. Can you help me with a formula in Excel to also check if month has changed and then move the invoice amount automatically to the next age group.

[royUK]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[daddylonglegs]

Theoretically if the invoice date is in the category <=30 (days ago) then that date could be in the current month, last month or even the month before that (e.g. invoice date 31st Jan 2010, today's date 1st March 2010), so I don't know how you want to handle the latter case (if it's 2 months back should it be in 61-90?) but, ignoring that for a moment you could use this sort of approach:

This formula for <=30

=AND($L257<=30,MONTH(TODAY())=MONTH($A257))*$F257

and for 31-60

=AND($L257<=60,OR($L257>30,MONTH(TODAY())<>MONTH($A257)))*$F257

where column A contains the invoice dates

Note: if you don't need column L for other reasons then you could eliminate that column altogether and use =TODAY()-$A257 in the formulas in place of $L257

Edit: simplified suggested formulas