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]

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

OK, it looks like you changed the format again, and have records missing dates (which wasn't mentioned initially).
It is kind of hard to help you and give you an answer that works when you keep changing things!

Please provide us with the correct details, specifically:
- What columns exactly do each of the three dates appear in?
- What do non-date entries (like "N", "Yes", and blank) mean and how should they be treated (what do you want to happen in each of those instances)?
- If there is anything else that needs to be accounted for, please mention it here.

On a side note, if this is a form that people are filling out, I would recommend implementing Cell Validation on these date columns to prevent non-date entries from being entered.

 

[MarieEx]

Good morning,

Thank you for your help and sorry about the confusion.

I didn't change the format, I omitted cells for simplicity purposes, my original worksheet ends at column AY and I thought it'd be easier if I presented them here as A,B,C,D. Before I copied your formula into my sheet I changed the cells to the correct ones and ran it down.

The non-data Y entry means I do not yet have the date for the registration. The N entry means the person has either not registered and/or sent the Rxs in. With these, now that you mentioned them, maybe they can result in a different kind of result, maybe a "Missing".

If you look at the last sample I sent you, lines 9-16 (omitting #15 ) were received more than 30 days from the camp start date of June 18. These should NOT have the $10 late fee. I need the late fee to show up when the registration OR the Rxs were received 30 days or less from the camp start date.


Again thank you for help with this.

 

[Joe4]

I didn't change the format, I omitted cells for simplicity purposes, my original worksheet ends at column AY and I thought it'd be easier if I presented them here as A,B,C,D. Before I copied your formula into my sheet I changed the cells to the correct ones and ran it down.

OK. The fact that you were getting difference results than me on the same data tells me that you probably weren't making the proper adjustments to the formula. So, pay very close attention to the changes that you need to make to adapt it to your structure.

Here is a formula for your last example, that assumes that the dates are in column A, B, and E. So, place this formula on row 2 and copy down for all rows:

=IF(OR(A2="",ISTEXT(A2),B2="",ISTEXT(B2)),"Dates Missing",IF(OR(E2-A2<=30,E2-B2<=30),10,""))

When I applied this to your last set of test data, I got a mixture of 10 fees, blanks, and text of "Dates Missing".
It appeared to pull back the correct expected value in every instance.

If you do not see the same, please provide a specific example, and tell me what it is returning.

 

[MarieEx]

IT WORKED! Thank you so much! This saves me hours of mindless work.

 

[Joe4]

You are welcome.
Glad we were able to get it to work.