Accounts Receivable Aging in Excel

[jeffdlinn]

=IF(TODAY()-F2<=30,"0-30 days",IF(TODAY()-F2<=60,"31-60 days",IF(TODAY()-F2<=90,"61-90 days","over 90 days")))

The formula above works great for aging a report I have based on today's date of 7/9/2014. However the report was prepared on 7/1/2014, so I need to age based on 7/1/2014. How can I modify this formula to age based on the date of 7/1/2014.

Thanks

 

[vds1]

Your formula can also be smaller version like

=LOOKUP(TODAY()-F2,{0,31,61,91},{"0-30 Days","31-60 Days","61-90 Days","Over 90 Days"})


If you want 7/1/2014 instead of today then

=LOOKUP(DATE(2014,7,1)-F2,{0,31,61,91},{"0-30 Days","31-60 Days","61-90 Days","Over 90 Days"})


Your formula would be like for 7/1/2014

=IF(DATE(2014,7,1)-F2<=30,"0-30 days",IF(DATE(2014,7,1)-F2<=60,"31-60 days",IF(DATE(2014,7,1)-F2<=90,"61-90 days","over 90 days")))

 

[jeffdlinn]

I was close, I formatted my date incorrectly.

Your formula worked beautifully. Thanks!

 

[MAX123]

my company is a trading co and buys products from different companies... which formula I should be using to making aging report under their name and within days (i.e 1-30,31-60 and soo on)..plz do reply...thx