Create an aged column

[Grd]

Hi there,

I'm trying to create a formula that shows me the following based on the date
of invoice.

Aged 30
Aged 60
Aged 90
Aged 120
Aged 180
Aged 240 etc upto 360 then
Aged Over 1 year

This is a really tough if statement I think but I'm not so great at anything
other than basic if statements.

Any help would greatly appreciated.

Tx

Suzanne

 

[BoniM]

=HLOOKUP(TODAY()-A2,{0,30,60,90,120,180,240,300,360;"","Aged 30","Aged
60","Aged 90","Aged 120","Aged 180","Aged 240","Aged 300","Aged Over 1
Year"},2)

This function will subtract an invoice date in cell A2 (adjust as needed)
from today. If it has been less than 30 days, it will display nothing in the
cell, if it has been between 30 and 59 days, it will display - Aged 30,
between 60 and 89 - Aged 60, etc.
Until 360 or over - Aged Over 1 Year.
If it's only close to what you were looking for, let me know...

 

[Sandy Mann]

With the invoice date in A1 try:

=IF(OR(A1="",A1>=TODAY()),"",IF(DATEDIF(A1,TODAY(),"y")>0,
"Over 1 year","Aged "&TODAY()-A1))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Pete_UK]

Do you mean if the invoice is more than 30 days old, but less than 60 you
want to show Aged 30, or does this apply to any invoice up to 30 days old?
(Sorry, I'm not an accountant).

Pete

 

[Grd]

Perfect and a very elegant formula

Tx
S

=HLOOKUP(TODAY()-A2,{0,30,60,90,120,180,240,300,360;"","Aged 30","Aged
60","Aged 90","Aged 120","Aged 180","Aged 240","Aged 300","Aged Over 1
Year"},2)

This function will subtract an invoice date in cell A2 (adjust as needed)
from today. If it has been less than 30 days, it will display nothing in the
cell, if it has been between 30 and 59 days, it will display - Aged 30,
between 60 and 89 - Aged 60, etc.
Until 360 or over - Aged Over 1 Year.
If it's only close to what you were looking for, let me know...

 

[Grd]

Tx Sandy

The formula gives me the exact number of days which could also be useful.

 

[Grd]

Hi Pete,

tx for your reply
Its exactly how BonM has it above which is great. 30 to 60 it is aged 30 etc

tx
s

http://www.microsoft.com/wn3/aspx/p...ice/community/en-us/settings.xml&sdgloc=en-US