PDA

View Full Version : Excel Experts



Shelly
08-20-2008, 04:56 PM
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
08-20-2008, 05:43 PM
excel is not an invoice program

Shelly
08-20-2008, 06:08 PM
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
08-20-2008, 06:31 PM
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
08-20-2008, 08:25 PM
Shelly, I usually browse on www.mrexcel.com for my excel problems. Good luck!

Shelly
08-20-2008, 08:30 PM
Sigh...

Forget it.

TDMVPDPOY
08-20-2008, 11:30 PM
shelly cash in hand ftw

Shelly
08-21-2008, 10:19 AM
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
08-21-2008, 10:57 AM
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 :lol

Dex
08-21-2008, 12:33 PM
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 shit just makes my brain hurt.

Shelly
08-21-2008, 01:02 PM
Okay...I changed the formula to coincide with the cell # I was using and my result was just a date :bang 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! :lol

thanks!

ducks
08-21-2008, 02:21 PM
google alphabetically in excel

Shelly
08-21-2008, 05:25 PM
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
08-21-2008, 06:01 PM
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
08-21-2008, 06:32 PM
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... :sleep

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

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
08-21-2008, 07:14 PM
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 :oops

TDMVPDPOY
08-21-2008, 07:50 PM
I don't know if it makes a difference because everything else is working, but I'm using using Excel 97 :oops

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?? :lmao

Shelly
08-21-2008, 08:11 PM
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?? :lmao

I'll inform our CPA that he's wrong :spin

Shelly
08-21-2008, 08:14 PM
Snafu...your solution worked! Looks the 0-30 column was showing a date for some reason.

Thanks again for your help!

sabar
08-22-2008, 12:00 AM
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
08-22-2008, 03:52 AM
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 bullshit. as compared to remembering maths formulas which is easy.