Formula to calculate dates in specific cells

[lzweifel]

I am trying to create a simple spreadsheet which will tell me when Invoices are due. What I would like to do is enter a date of an invoice and each time I open the spreadsheet based on the current date the due dates will change. So, for example today is May 14th and I open the spreadsheet. An invoice I entered on January 8th will show under the column 120 days on May 8th..... an invoice on Feb 2 will show over 90 days on May 3rd and invoice entered on April 10 with show Over 30 days.

There may be a better way, no doubt.... but what I am looking for is to open the spreadsheet and see the dates in which everything is due. So I could put formulas in to calculate 30 days, 60 days, 90 days etc. But if that particular invoice is now 90 days based on the day I open the spreadsheet I do not want it to display the date in the 30 or 60 day column.

Hope someone can give me some assistance, I will appreciate it very much!

Lorie

 

[Caribeiro77]

Can you post a sample of how's your data is set it up?
Use Mr excel HTML maker( see how on my signature)

 

[RonB1111]

With your data laid out like this:

	A	B	C	D	E	F	G	H
1	Customer	Invoice Date	Amount	0-30 days	31-60 days	61-90 days	91-120 days	over 120 days
2	ABC Corp	4/30/2015	100.00	100	0	0	0	0
3	DEF Corp	3/27/2015	200.00	0	200	0	0	0
4	GHI Corp	2/25/2015	300.00	0	0	300	0	0
5	JKL Corp	1/20/2015	400.00	0	0	0	400	0
6	MNO Corp	11/30/2014	500.00	0	0	0	0	500

<colgroup><col><col><col><col><col span="5"></colgroup><tbody>
</tbody>

Enter these formula and copy down:

In D2:

=IF(TODAY()-B2<31,C2,0)

In E2:

=IF(AND(TODAY()-B2>30,TODAY()-B2<61),C2,0)

In F2:

=IF(AND(TODAY()-B2>60,TODAY()-B2<91),C2,0)

In G2:

=IF(AND(TODAY()-B2>90,TODAY()-B2<121),C2,0)

In H2:

=IF(TODAY()-B2>120,C2,0)

 

[SpillerBD]

Calculate your aging, then push into a Pivot table for your reporting.