calling all the Excel experts!

[luvya]

Hi, I am currently working in an accounting firm...one of my duty is to call out people about the past due check. We have 3 columns for 0-30days.....30-60 days......60-90 days............Since most terms are 30 days....naturally, I would start making phone call if a check is not receive in 30 days.....and it gets kind of annoying...I have to move the amount from let's say 0-30 to 30-60 manually..and it's a pain in the @$$ considering that we have more than 200 customer profiles. So is there any formula that I can use so that Excel can move the amount according to the invoice past due day? Please help!!!!!!!!!!

 

[Bglad]

Um... don't accountants just do annoying things like that all day anyway?

I can't figure out how to make Excel move the number. To be specific, it won't "move" a number without writing a script. You have to enter an argument and the IF function only has one argument so it won't work. Furthermore, it doesn't seem to work if you tell it 30<B1<60 to find out if it is between 30 and 60 days overdue.

The only way I see to do it is to create a column for days past due:

Assume cell A1 is the receipt amount in $.

Say cell B1 is the receipt date. You must enter the date as a formula for Excel to be able to do calculations with the date. Just formatting the cell as date will not work. For example, March 5, 2002 would be "=DATE(2002,3,5).

Cell C1 would tell you the number of days overdue by entering "=TODAY()-B1"

Then if you wanted to keep your 3 past due columns:
Add a column to figure days overdue as above. Keep your columns for 30, 60, 90 days overdue. Say cell D1 is for 30 days. Enter "=AND(C1>29,C1<60)". This will answer TRUE if it is between 30 and 60 days. Just change the formula for the 60 and 90 days.

I can't figure out how to make it just move the amount. Maybe someone else can teach me a lesson now.

 

[Jackhamr60504]

Being the lazy type, I would have the value in all three cells and use conditional formatting to hide the values I don't want to see (white on white)

Hope that helps.

 

[CPA]

BGlad,

I don't know if you couldn't use the IF statement. Without actually throwing it together, I would try putting the vendor balance in a column (then hide it). Then using the IF statement with a formula containing a cell containing TODAY's date and the invoice date from another cell could make it work.

For Example

A1 = TODAY()

B3= Vendor date

C3 = Amount owed (hidden)

D3 = IF((A1 - B3) < 30, C3, "")

E3 = IF((A1 - B3) < 60 && > 30, C3, "")

and so on...
Now this would not work if you maintained multiple invoices in one cell, but could work if each Row contained one unique invoice

 

[NeoV]

bglad has the right idea here.....the amount owed is in one column, the next column has the original due date, and then you have three columns for your age categories.....

You would also need a cell above this data for the current date

your three columns would have an "if" function in them...


today = 3/5/02
$ due orig due date 0-30 31-60 61+
100 11/11/2001 0 0 100
50 12/31/2001 0 0 50
75 2/24/2002 75 0 0

Now, the cell for today has the function "=now()", and is cell b1

Column C is the "0-30" column....the formula there would be "=IF(DAYS360($B3,$B$1,FALSE)<31,A3,0)"
Column D is the "31-60" column....the formula there would be " =IF(C3>0,0,IF(DAYS360($B3,$B$1,FALSE)>30,IF(DAYS360($B3,$B$1,FALSE)<61,A3,0)))"

Column E is the "61+" column...the formula there would be " =IF(C3>0,0,IF(D3>0,0,IF(DAYS360($B3,$B$1,FALSE)>60,A3,0)))"

remember, the format for "if" statements is (if what - true - false), with each part divided by commas...

Let me know if you have any questions.

 

[NeoV]

it happened again!

Why is it when I copy/paste from excel, when I am editing or typing the message the numbers appear to be aligned, but when I post the thread, they are all out of order?

 

[Bglad]

Well now this is just a logic problem that I have to solve.

Conditional formatting does not help because you can't format based on the condition of another cell. i.e. you can't white out the 30 days column which holds a $ amount based on the days overdue column.

Neo, you and I are thinking on the same lines. Problem is your formula for 30 days (=IF(DAYS360($B3,$B$1,FALSE)<31,A3,0)) doesn't work. It looks like it should work, but it doesn't. Try it. It returns the amount regardless of the days overdue. Second, it will leave the amount in that column even if it is over 60 days overdue. It needs a second argument.

I tried the same theory with a different and slightly simpler formula because I used the days overdue column.

A1: receipt date "=DATE(2002,2,1)"
B1: $ amt
C1: Today "=TODAY()"
D1: days overdue "=DAYS360(A1,C1)"
E1: 30 days "IF(29<D1<60,B1,0)"
F1: 60 days "IF(59<D1<90,B1,0)"

But this doesn't work either. It gives a 0 result regardless of days overdue, the opposite of your formula. I can't figure out why. Only thing I can figure is IF doesn't like multiple arguments.

 

[Slickster]

bglad, you're in the right direction. Here's the answer:

A1: receipt date "=DATE(2002,2,1)"
B1: $ amt
C1: Today "=TODAY()"
D1: days overdue "=DAYS360(A1,C1)"
E1: 0 - 30 days "=IF(D1<30,B1,"")"
F1: 31 - 60 days "=IF(AND(D1>30,D1<60),B1,"")"
G1: 61 - 90 days "=IF(D1>60,B1,"")"

I hope this helps, if not let me know.

Slickster

 

[Bglad]

ARGH!!!!!

Slickster got it!!!

 

[NeoV]

mine worked too...the output of it is in my first post, and although the columns didn't align correctly, the first two amounts show "0" "0" for the 0-30 and 31-60 columns...