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.