date formula

[cedius]

I have somewhat of a billing project im doing for class and I am stumped on just this last section.What I am trying to do is add a late formula.In the spreadsheet I have a column with a dated title.When I input the date in that column,I want to know if the person has aquired a late fee.
May be this will help.
If > 05-31-02 = Then No Late Fee
If < 06-01-02 = Then $40 late Fee
I want to input the date and have the late fee info show in a designated cell.
Also, will I have to input this formula in every cell, I have 100.

Thanx, hope I explained it correctly.

[crimlet]

On 2002-05-23 02:26, cedius wrote:
I have somewhat of a billing project im doing for class and I am stumped on just this last section.What I am trying to do is add a late formula.In the spreadsheet I have a column with a dated title.When I input the date in that column,I want to know if the person has aquired a late fee.
May be this will help.
If > 05-31-02 = Then No Late Fee
If < 06-01-02 = Then $40 late Fee
I want to input the date and have the late fee info show in a designated cell.
Also, will I have to input this formula in every cell, I have 100.

Thanx, hope I explained it correctly.

try putting this in the cell you want the information to appear
=if(cell the date is in <6/01/02,concatenate("fee recieved on ",cell reference," No fee"),("fee recieved on ",cell reference," $40 fee"))
although you may want to check the format of your date column



[ This Message was edited by: crimlet on 2002-05-23 02:48 ]

[cedius]

I cant seem to get that code to work.
Is there another way. I keep getting the formula error popup.

[Yogi Anand]

On 2002-05-23 02:26, cedius wrote:
I have somewhat of a billing project im doing for class and I am stumped on just this last section.What I am trying to do is add a late formula.In the spreadsheet I have a column with a dated title.When I input the date in that column,I want to know if the person has aquired a late fee.
May be this will help.
If > 05-31-02 = Then No Late Fee
If < 06-01-02 = Then $40 late Fee
I want to input the date and have the late fee info show in a designated cell.
Also, will I have to input this formula in every cell, I have 100.

Thanx, hope I explained it correctly.

Hi Cedius:
Can you state in words about the late fee situation? Is the fee imposed if a bill is not paid in time? Instead of using the less than and greater than symbols, please state it in words what is the late fee and when is it imposed.

Regards!

[cedius]

yes, basically, payment is to be due before the first, like rent.If I receive payment after the first, a late fee is imposed of $40.00.So I have it set up where I input the date I recieved payment.If the date is before 6-01-02, no late fee, But if it is after 6-01-02, In another cell, I want to see a late fee show up of $40.

Hope that helps

[Yogi Anand]

On 2002-05-23 12:04, cedius wrote:
yes, basically, payment is to be due before the first, like rent.If I receive payment after the first, a late fee is imposed of $40.00.So I have it set up where I input the date I recieved payment.If the date is before 6-01-02, no late fee, But if it is after 6-01-02, In another cell, I want to see a late fee show up of $40.

Hope that helps

Hi Cedius:
I am going to reinterpret the criteria you stated -- Excel is very finicky abou this -- it can not stand any ambiguity. So here goes how I understand your criteria ...

'If Payment Received date is greater than 6/1/2002, there will be a fee of $40.'

The other things you mentioned e.g. if the payment is received on 5/31/02, there will be no LateFee, and if the payment is received after 6/1/02 there will be a Latefee of $40 are covered by the statement noted above.

Now having a clear understanding of this, we can write a simple formula to cover this situation -- see the worksheet simulation ...

Formula used:

="Late Fee = $"&(B3>$B$1)*40

Microsoft Excel - y020523.xls
A1		=	CutOffDate
	A	B	C	D
1	CutOffDate	:alert('37408')>6/1/02
2
3	ReceiptDate	:alert('37407')>5/31/02	:alert('="Late Fee = $"&(B3>$B$1)*40')>Late Fee = $0
4	ReceiptDate	:alert('37408')>6/1/02	:alert('="Late Fee = $"&(B4>$B$1)*40')>Late Fee = $0
5	ReceiptDate	:alert('37409')>6/2/02	:alert('="Late Fee = $"&(B5>$B$1)*40')>Late Fee = $40
6
7
8
Sheet6

Click on the hyperlinked cell to see the underlying formula

Regards!

_________________
Yogi Anand

Edit: Deleted inactive website from hardcoded signature

[ This Message was edited by: Yogi Anand on 2003-01-19 12:56 ]

[Aladin Akyurek]

On 2002-05-23 12:04, cedius wrote:
yes, basically, payment is to be due before the first, like rent.If I receive payment after the first, a late fee is imposed of $40.00.So I have it set up where I input the date I recieved payment.If the date is before 6-01-02, no late fee, But if it is after 6-01-02, In another cell, I want to see a late fee show up of $40.

Hope that helps

Why not just use:

=(E2>criterion-date)*40

where E2 is the date the payment is received and criterion-date is a cell holding the criterion date after which a fee is imposed.

This formula will give you either 0 (no fee) or 40, which you can use in other formulas.


[ This Message was edited by: Aladin Akyurek on 2002-05-23 22:25 ]