Late fee IF 30 days LESS than two different dates

[MarieEx]

Good day to you all,

I need help. I am trying to create a column for late fees, if either Registration Date (C) or RX received (D) are LESS than 30 days from Camp Start date (I). Basically if we receive either the registration or the Rx less than 30 days from camp session start date I have to charge a late fee.

So far this is what I can come up with, but the info that is giving me is incorrect.

=IF(OR(A2<=(E2-30),B2<=(E2-30)),"$10.00","")

Thanks in advance[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Registration Date[/TD]
[TD]RX Received Date[/TD]
[TD]Late Fee[/TD]
[TD]OTC/RX[/TD]
[TD]Camp Session Start Date[/TD]
[TD]Camp Name[/TD]
[TD]Duration[/TD]
[TD]SIB[/TD]
[/TR]
[TR]
[TD]05/29/19[/TD]
[TD]06/01/19[/TD]
[TD]?[/TD]
[TD]OTC[/TD]
[TD]06/16/19[/TD]
[TD]ABC[/TD]
[TD]14[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]04/15/19[/TD]
[TD]05/09/19[/TD]
[TD]?[/TD]
[TD]RX[/TD]
[TD]06/16/19[/TD]
[TD]ABC[/TD]
[TD]28[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]05/01/19[/TD]
[TD]05/09/19[/TD]
[TD]?[/TD]
[TD]RX[/TD]
[TD]06/16/19[/TD]
[TD]ABC[/TD]
[TD]17[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]06/01/19[/TD]
[TD]06/04/19[/TD]
[TD]?[/TD]
[TD]RX[/TD]
[TD]06/16/19[/TD]
[TD]ABC[/TD]
[TD]10[/TD]
[TD]Y[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

Welcome to the Board!

Try this:

[COLOR=#333333]=IF(OR(E2-A2<=30,E2-B2<=30),10,"")[/COLOR]

Also, if you want to do math on the values you are returning, return the number 10, not the text value "$10.00" (anything between double-quotes is treated as literal text)
To get the dollar sign and decimals, simply apply cell formatting to that cell.

 

[MarieEx]

Welcome to the Board!

Try this:

[COLOR=#333333]=IF(OR(E2-A2<=30,E2-B2<=30),10,"")[/COLOR]

Also, if you want to do math on the values you are returning, return the number 10, not the text value "$10.00" (anything between double-quotes is treated as literal text)
To get the dollar sign and decimals, simply apply cell formatting to that cell.

--- Thank you for your reply but it did not work I gives me a 10 on every single line, which I know is not correct.

 

[Joe4]

In your example, the first and the fourth record return 10 for me, while the other two do not.

If you don't see the same thing, you may have a data issue. Are A2, B2, and E2 really entered as dates, or text?
What do these formulas return?
=ISNUMBER(A2)
=ISNUMBER(B2)
=ISNUMBER(E2)

 

[MarieEx]

They return FALSE, all 3

 

[MarieEx]

NO I was wrong. They return TRUE on mine. I'm sorry I had pasted it onto the wrong cell

 

[Joe4]

Check the following:
- You typed in the formula correctly
- Your formula is referencing the correct columns
- Your formula is referencing the correct row

After you verify that, if you still feel it is not working properly, then provide me with an example that is not working.

 

[MarieEx]

All three items you asked are correct. Bellow is a copy/paste of the actual sheet. As you can see there is a 10 on all lines. The 04/15/19 Registrations shouldn't have the 10 because the camp session starts 06/18/19, and we received registration on 04/15 and Rxs on 04/18... well before the 30 day window (which would be 05/18/19). Thank you so much for all your help.


[TABLE="width: 946"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Reg Date [/TD]
[TD]Rxs Rec Date[/TD]
[TD]late fee[/TD]
[TD]# of rxs needed[/TD]
[TD]OTC or Rx[/TD]
[TD]Camp Session Name[/TD]
[TD]Camp Session Start Date[/TD]
[/TR]
[TR]
[TD]5/29/2019[/TD]
[TD]5/31/2019[/TD]
[TD]$10.00[/TD]
[TD]1[/TD]
[TD]Rx[/TD]
[TD]Full Session[/TD]
[TD]6/16/2019[/TD]
[/TR]
[TR]
[TD]5/29/2019[/TD]
[TD]5/31/2019[/TD]
[TD]$10.00[/TD]
[TD]1[/TD]
[TD]Rx[/TD]
[TD]Full Session[/TD]
[TD]6/16/2019[/TD]
[/TR]
[TR]
[TD]5/29/2019[/TD]
[TD]5/31/2019[/TD]
[TD]$10.00[/TD]
[TD]1[/TD]
[TD]OTC[/TD]
[TD]Full Session[/TD]
[TD]6/16/2019[/TD]
[/TR]
[TR]
[TD]5/29/2019[/TD]
[TD]5/31/2019[/TD]
[TD]$10.00[/TD]
[TD]1[/TD]
[TD]OTC[/TD]
[TD]Full Session[/TD]
[TD]6/16/2019[/TD]
[/TR]
[TR]
[TD]5/29/2019[/TD]
[TD]5/31/2019[/TD]
[TD]$10.00[/TD]
[TD]1[/TD]
[TD]OTC[/TD]
[TD]Full Session[/TD]
[TD]6/16/2019[/TD]
[/TR]
[TR]
[TD]5/29/2019[/TD]
[TD]5/31/2019[/TD]
[TD]$10.00[/TD]
[TD]1[/TD]
[TD]OTC[/TD]
[TD]Full Session[/TD]
[TD]6/16/2019[/TD]
[/TR]
[TR]
[TD]4/15/2019[/TD]
[TD]4/18/2019[/TD]
[TD]$10.00[/TD]
[TD]1[/TD]
[TD]OTC[/TD]
[TD]Session 1[/TD]
[TD]6/18/2019[/TD]
[/TR]
[TR]
[TD]4/15/2019[/TD]
[TD]4/18/2019[/TD]
[TD]$10.00[/TD]
[TD]1[/TD]
[TD]Rx[/TD]
[TD]Session 1[/TD]
[TD]6/18/2019[/TD]
[/TR]
[TR]
[TD]5/17/2019[/TD]
[TD]5/15/2019[/TD]
[TD]$10.00[/TD]
[TD]1[/TD]
[TD]OTC[/TD]
[TD]Session 1[/TD]
[TD]6/18/2019[/TD]
[/TR]
[TR]
[TD]4/26/2019[/TD]
[TD]5/13/2019[/TD]
[TD]$10.00[/TD]
[TD]1[/TD]
[TD]Rx[/TD]
[TD]Session 1[/TD]
[TD]6/18/2019[/TD]
[/TR]
[TR]
[TD]4/26/2019[/TD]
[TD]5/13/2019[/TD]
[TD]$10.00[/TD]
[TD]1[/TD]
[TD]Rx[/TD]
[TD]Session 1[/TD]
[TD]6/18/2019[/TD]
[/TR]
[TR]
[TD]3/12/2019[/TD]
[TD]5/6/2019[/TD]
[TD]$10.00[/TD]
[TD]1[/TD]
[TD]Rx[/TD]
[TD]Session 1[/TD]
[TD]6/18/2019[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[MarieEx]

Check the following:
- You typed in the formula correctly
- Your formula is referencing the correct columns
- Your formula is referencing the correct row

After you verify that, if you still feel it is not working properly, then provide me with an example that is not working.

___________________________________________________________________________

Following please my actual columns. The Late Fee column is filled in with your formula.


[TABLE="width: 477"]
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Reg DATE[/TD]
[TD][/TD]
[TD]All Rxs Rec DATE[/TD]
[TD][/TD]
[TD]LATE FEE[/TD]
[TD][/TD]
[TD]Session[/TD]
[TD][/TD]
[TD]Camp Session Start Date[/TD]
[/TR]
[TR]
[TD]6/1/2019[/TD]
[TD][/TD]
[TD]N[/TD]
[TD][/TD]
[TD]#VALUE![/TD]
[TD][/TD]
[TD]A [/TD]
[TD][/TD]
[TD]6/5/2019[/TD]
[/TR]
[TR]
[TD]6/1/2019[/TD]
[TD][/TD]
[TD]N[/TD]
[TD][/TD]
[TD]#VALUE![/TD]
[TD][/TD]
[TD]A [/TD]
[TD][/TD]
[TD]6/5/2019[/TD]
[/TR]
[TR]
[TD]5/29/2019[/TD]
[TD][/TD]
[TD]5/31/2019[/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[TD]A [/TD]
[TD][/TD]
[TD]6/16/2019[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD]5/31/2019[/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[TD]A [/TD]
[TD][/TD]
[TD]6/16/2019[/TD]
[/TR]
[TR]
[TD]5/29/2019[/TD]
[TD][/TD]
[TD]5/31/2019[/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[TD]A [/TD]
[TD][/TD]
[TD]6/16/2019[/TD]
[/TR]
[TR]
[TD]5/29/2019[/TD]
[TD][/TD]
[TD]5/31/2019[/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[TD]A [/TD]
[TD][/TD]
[TD]6/16/2019[/TD]
[/TR]
[TR]
[TD]5/29/2019[/TD]
[TD][/TD]
[TD]5/31/2019[/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[TD]A [/TD]
[TD][/TD]
[TD]6/16/2019[/TD]
[/TR]
[TR]
[TD]5/29/2019[/TD]
[TD][/TD]
[TD]5/31/2019[/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[TD]A [/TD]
[TD][/TD]
[TD]6/16/2019[/TD]
[/TR]
[TR]
[TD]4/15/2019[/TD]
[TD][/TD]
[TD]4/18/2019[/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[TD]A [/TD]
[TD][/TD]
[TD]6/18/2019[/TD]
[/TR]
[TR]
[TD]4/15/2019[/TD]
[TD][/TD]
[TD]4/18/2019[/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[TD]A [/TD]
[TD][/TD]
[TD]6/18/2019[/TD]
[/TR]
[TR]
[TD]5/17/2019[/TD]
[TD][/TD]
[TD]5/15/2019[/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[TD]A [/TD]
[TD][/TD]
[TD]6/18/2019[/TD]
[/TR]
[TR]
[TD]4/26/2019[/TD]
[TD][/TD]
[TD]5/13/2019[/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[TD]A [/TD]
[TD][/TD]
[TD]6/18/2019[/TD]
[/TR]
[TR]
[TD]4/26/2019[/TD]
[TD][/TD]
[TD]5/13/2019[/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[TD]A [/TD]
[TD][/TD]
[TD]6/18/2019[/TD]
[/TR]
[TR]
[TD]3/12/2019[/TD]
[TD][/TD]
[TD]5/6/2019[/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[TD]A [/TD]
[TD][/TD]
[TD]6/18/2019[/TD]
[/TR]
[TR]
[TD]YES[/TD]
[TD][/TD]
[TD]YES[/TD]
[TD][/TD]
[TD]#VALUE![/TD]
[TD][/TD]
[TD]A [/TD]
[TD][/TD]
[TD]6/18/2019[/TD]
[/TR]
[TR]
[TD]4/15/2019[/TD]
[TD][/TD]
[TD]4/18/2019[/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[TD]A [/TD]
[TD][/TD]
[TD]6/18/2019[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

All three items you asked are correct. Bellow is a copy/paste of the actual sheet. As you can see there is a 10 on all lines. The 04/15/19 Registrations shouldn't have the 10 because the camp session starts 06/18/19, and we received registration on 04/15 and Rxs on 04/18... well before the 30 day window (which would be 05/18/19).

OK, in the previous email, I mentioned:

Check the following:
- You typed in the formula correctly
- Your formula is referencing the correct columns
- Your formula is referencing the correct row

In your data example, you have 2 extra columns, so the Start Date is NOT in column E, but looks like it is in column G. So you would need to adjust the formula (since the format is not the same as your original).
That formula would look like:

=IF(OR([COLOR=#ff0000]G2[/COLOR]-A2<=30,[COLOR=#ff0000]G2[/COLOR]-B2<=30),10,"")

(assuming that you do not have any other extra columns).

If you do that, then it shows the first 6 records as having a late fee, and the last 6 records not having a late fee, which seems correct, according to your rules.