Help Please?

M

Marben308

Hi I am a student and am working on a excel project. Could someone sho
me how to make up a formula? In cell8, I need to enter IF function wit
nested TODAY function to test the condition in the column heading? Als
in cell E8 enter IF function with nested AND and TODAT functions t
test the condition in the column heading? I have no idea how to pu
this function together and my textbook isn't much help. Thank you!!

cells)
1) Accounts Receivable

2) Customer Tracking
3) 04/27/04
4)
5)
6) Amount <=30 >30 and <=60 >60 and <=90 >90
7) Customer Owed Due Date Days Days Days Days
I. Bergquist $850.00 Mar 3, 04
9) T. Thomas 1,250.00 Apr 17, 04
10) J. Mathers 655.00 Mar 23, 04
11) C. Clark 750.00 Jan 31, 04
12) F. Adams 550.00 Jan 23, 0
 
K

Ken Wright

Tell you what I'll do, I'll give you an example of how the formulas work and you
see if you can adapt it for your situation.

Assume in cell A1 I have a date, and I want to know if it is more than 30 days
old. In any other cell, I can use

=IF((TODAY()-A1)>30,"Older than 30 Days","Invoice not yet Due")

The IF function takes the form =IF(Condition, If TRUE do this, Else if FALSE do
this)

As far as the dates go, Excel treats days as whole numbers from Jan 1 1900, so
5th Jan 1900 was 5, 24th Jan 1900 was 24, and today is 38,104, ie 38,104 days
since 1st jan 1900. So if you for example had the date 15th Apr 04 in cell A1,
as far as excel is concerned that date is equal to the number 38,092, so if you
had the following formula:-

=TODAY()-A1

that is the same as writing:-

=38,104-38,092 = 12 (days)

So, with the formula from above, plus what i just gave you, you have

=IF((TODAY()-A1)>30,"Older than 30 Days","Invoice not yet Due")

In this case the answer is 12, so it is NOT true, so it will use the second
argument and put 'Invoice not yet Due' in the cell.

From there it's just simple maths and working out the plusses and minuses to get
you where you need
 
M

Marben308

Thank you Ken & Charlie!! I will try it again. I really appreciate you
time and help!! Have a great day!! Mari
 
K

Ken Wright

You're welcome - Have a go at adapting it, but please feel free to post back
with specific questions if you have trouble with it.
 
M

Marben308

I just can't figure this out. I do not know how to build the formula.
just started Excel 2000 in school and I am not a kid:rolleyes: lol. Bu
thank you so much for your help. I will keep trying but have no clue
Have a great night!! Marie :confused
 
L

Lady Layla

EAsiest way to build a formula in Excel is to use the Formula Wizard, look on
your toolbar and see if you have a button that looks like a backward 3
(autosum) with a triangle pointing down next to it. If so, click on the
triangle and select more functions

Another place to find help is within Excel Help, go in there and select Function
help



: I just can't figure this out. I do not know how to build the formula. I
: just started Excel 2000 in school and I am not a kid:rolleyes: lol. But
: thank you so much for your help. I will keep trying but have no clue.
: Have a great night!! Marie :confused:
:
:
: ---
:
:
 
M

Marben308

:) I got the formula finally but with my teachers help. I never would o
figured this one out. We haven't even gone thru this type of thin
before. =IF(AND(TODAY()-C8<=30,B8,"") Thank you everyone and specia
thanks to Ken, you tried to show me but I was lost!! Have a great da
everyone!!! ;) Mari
 
Top