Excel Experts

[Shelly]

If my husbands accounting wasn't on a cash basis, I could just post my invoices and run an A/P aged report. But since I cannot do that, I was wondering if someone could write me a formula for an aging spread sheet I have.

I need cell B9 (which is the invoice date) to take cell D9 (which is the invoice amount) and age it 30 days, 60 days, 90 days. So if the invoice was 30 days old, it would show in the the 30 day column, etc.

Thanks in advance.

[ducks]

excel is not an invoice program

[Shelly]

No kidding, ducks. That's why I have an accounting program that does that for me. But since I cannot have open invoices sitting on my books, I'm looking for a way to age them. I'm sure there is a formula to do so, which is why I'm asking for help.

[ducks]

No kidding, ducks. That's why I have an accounting program that does that for me. But since I cannot have open invoices sitting on my books, I'm looking for a way to age them. I'm sure there is a formula to do so, which is why I'm asking for help.

what kind of accounting program are you using
you maybe able to save the excel invoices into .csv files and import them into your accounting program

or you could google what you want
it might find it for you

I wanted all lower case instead of uppercase one time and I googled it and it worked

[Ginofan]

S y, I usually browse on www.mrexcel.com for my excel problems. Good luck!

[Shelly]

Sigh...

Forget it.

[TDMVPDPOY]

s y cash in hand ftw

[Shelly]

I did try looking online and there were so many different formulas that people were giving on different websites that I got confused and just gave up.

Snafu, that's exactly what I'm looking for! I'll give it a try and let you know how it works. Thanks!

[Shelly]

Thanks!

I use MAS90, which obviously can handle aging. It's the program i use to do my dad's accounting, which is accrual basis. Since you can have up to 99 companies on it, I just use it for my husband's stuff also.

I have a love/hate relationship with MAS90

[Dex]

E9 formula: =IF((AND(($A$5-B9)>29,($A$5-B9)<60)), D9," ")
F9 formula: =IF((AND(($A$5-B9)>59,($A$5-B9)<90)), D9," ")
G9 forumla: =IF(($A$5-B9)>89, D9," ")

The fact that this makes sense is why I hate Excel.

I've coded C++, HTML, Java, and a tidbit of XML...and this just makes my brain hurt.

[Shelly]

Okay...I changed the formula to coincide with the cell # I was using and my result was just a date I never really paid attention, but do aging reports age from invoice date, due date, or current?

Here's how my template is set up:

A2 = today's date used =TODAY() forumla. Or should I just input that manually?
A9 = vendor
B9 = invoice date
C9 = terms (not sure if I really need. MAS90 knows the terms from vendor profile, but I do want to see the due date)
D9 = Due date
E9 = invoice amount
G9 = 0-30 days
H9 = 31-60 days
I9 = 61-90 days

I would also love to have it sorted alphabetically, but I realize that may be asking a bit too much!

thanks!

[ducks]

google alphabetically in excel

[Shelly]

Snafu, I sincerely thank you from the bottom of my heart!! It's working exactly how I wanted and will make keeping track of the invoices a lot easier. I was trying to keep track manually via a calendar and it just was not working for me.

Thanks again for taking the time to do this for me! I really appreciate it!

[Shelly]

Everything is working great, execpt for the 0-30 column. It's throwing in a date of 01/30/00 instead of the invoice amount.

[Shelly]

0-30 column [F9]: =IF(($A$2-B9)<31,C9," ")

this says that IF the dates are less than 31 days apart, then put C9 here (the Inv. Amount), ELSE just put a blank space here (" ")

don't know why your other columns are working and this one isn't. Bet it's something simple. Post your 0-30 formula.

I was bored all day at work, I should be thanking you for giving me something to do...

I never noticed this Geek Zone forum until just the other day...

The column is actually G9, but I even tried it in F9. I'm pretty sure I just copied and pasted, but this is the formula:

=IF(($A$2-B9)<31,C9," ")

edited to add...the invoice amount is in E9, but if I change C9 to E9, it still gives me a date

[Shelly]

Okay, changing to E9 still gives me a date.

A2 is =TODAY()
B9 is my invoice date in 08/21/08 format
E9 is my invoice amount in 100.00 format
G9 (0-30 days) will give me some random date in 00/00/00 format. The year is always 00

I don't know if it makes a difference because everything else is working, but I'm using using Excel 97

[TDMVPDPOY]

I don't know if it makes a difference because everything else is working, but I'm using using Excel 97

get an upgrade....you can get the 08 office student edition for 130 or cheaper if you look around

ps. ur mans business is accrual, credit sales only need to be declared when received....you can pull a dogedy and write it all off as bad debts, even though you had received it...does ur man want a cell next to wesley snipes??

[Shelly]

get an upgrade....you can get the 08 office student edition for 130 or cheaper if you look around

ps. ur mans business is accrual, credit sales only need to be declared when received....you can pull a dogedy and write it all off as bad debts, even though you had received it...does ur man want a cell next to wesley snipes??

I'll inform our CPA that he's wrong

[Shelly]

Snafu...your solution worked! Looks the 0-30 column was showing a date for some reason.

Thanks again for your help!

[sabar]

Back in the 1980s (I'm an old guy) I was a COBOL and RPG II programmer. I've dabbled in OOP off and on in the past few years, but I just can't wrap my brain around the OOP concept. I've got a linear-thinking brain... like COBOL.

Heh you could write functions like IF() in pretty much any OOP language. That would get ridiculously confusing with a large program though.

[TDMVPDPOY]

Heh you could write functions like IF() in pretty much any OOP language. That would get ridiculously confusing with a large program though.

this is why i hated doing formulas and remembering them, especially for excel, acess and sql bull . as compared to remembering maths formulas which is easy.