| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create an aged column
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Create an aged column
=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" wrote: 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Create an aged column
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 Replace @mailinator.com with @tiscali.co.uk "Grd" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Create an aged column
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" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Create an aged column
Perfect and a very elegant formula
Tx S "BoniM" wrote: =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" wrote: 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Create an aged column
Tx Sandy
The formula gives me the exact number of days which could also be useful. "Sandy Mann" wrote: 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 Replace @mailinator.com with @tiscali.co.uk "Grd" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Create an aged column
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 "Pete_UK" wrote: http://www.microsoft.com/wn3/aspx/po...l&sdgloc=en-US 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" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Column has emails & other stuff. Want to create a column w/ just e | Excel Worksheet Functions | |||
| Aged Debt - grouping values by date range | Excel Discussion (Misc queries) | |||
| how do I update a column and create new rows for new column cells | Excel Discussion (Misc queries) | |||
| how do I create multiple column width in the same column in excel | Excel Discussion (Misc queries) | |||
| How do I create column headings in Excel in the A, B, C etc column | Excel Discussion (Misc queries) | |||
Linear Mode
