Formula Mixed up??

ah2024_2024

New Member
Joined
Jun 18, 2024
Messages
23
Office Version
  1. 365
Platform
  1. Windows
First off, I'm not able to download anything on my work computer so I will only be able to utilize a screenshot.

Column AS (Deal in 1st 2 Weeks) has this formula: =IF(AND($AP2,">=2/1/2025",$D2<=$AR2,$AQ2=2,$O2,"<>*Flat rate"),"YES","NO") and all the piece but the first piece that references >=2/1/2025 works.

But notice that the first line of data that START DATE (column AP) IS greater than or equal to the date of 2/1/2025 and AS (where my formula is) correctly displays a YES.

But the next line of data that has a start date BEFORE 2/1/2025 is also displaying a YES in AS but should be giving me a NO since the start date IS NOT greater than or = 2/1/2025

Where am I going wrong with the formula? I've tried to change it to be AP>=2/1/2025 and gives the same result of a yes.

Basically, the formula is should be saying if this person started 2/1/2025 forward, then do the rest of what I need/am looking for.


1738788856227.png

1738788867540.png
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
May be you are trying for this,

=IF(AND($AP2>=DATE(2025,2,1), $D2<=$AR2, $AQ2=2, NOT(ISNUMBER(SEARCH("Flat rate", $O2)))),"Yes","No")
 
Upvote 0
May be you are trying for this,

=IF(AND($AP2>=DATE(2025,2,1), $D2<=$AR2, $AQ2=2, NOT(ISNUMBER(SEARCH("Flat rate", $O2)))),"Yes","No")
Thanks, but this updated one yield a NO for everyone. So, my one that was correctly throwing a yes now is a no.
 
Upvote 0
I entered the data you show in lines 2 and 3, and used Sam's formula, and it returned "Yes" for the first one and "No" for the second, which appears to be exactly what you want. i.e.

1738957410466.png


If it does not return what is shown in red for you, then assuming you don't have any typos in your formula (you should use Copy/Paste to enter Sam's formula, don't type it out manually), you may have some data issues such as:
1. Dates entered as text and not Dates
2. Dates that have time components that are being hidden by your chosen format
3. Numbers that have decimals with them that are being hidden by your chosen format

I recommend breaking apart each part of the AND function (entering it as its own function), to see what it returns, and verify each piece is correct.
For the one that is supposed to be returning "YES", every piece of the AND should return TRUE.
 
Upvote 0

Forum statistics

Threads
1,226,453
Messages
6,191,134
Members
453,642
Latest member
jefals

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top