Using Dates in an IF statement - HELP!

learningexcelnow

New Member
Joined
Oct 4, 2006
Messages
2
Hello,

I'm trying to help a salesperson solve a problem. He has a list of sales reps with a column for employment date. There is also a column for percent to goal of sales.

A B C

2/4/2006 86% value should be no
3/5/2004 107% value should be no
9/2/2006 101% value should be yes

We need to identify the sales rep that started employment in 2006...not prior, and has at least 100% or more of their sales goal. Below is the formula I have been playing with in Column C, but the date portion is not working.

=IF((AND(A1<=1/1/2006,B1>=100%)),"yes","no")

Thanks in advance for any help!
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
Try:

=IF((AND(A1<="1/1/2006"+0,B1>=100%)),"yes","no")
 

SamBo1234

Board Regular
Joined
Aug 21, 2006
Messages
77
You said, you needed to identify Sales Reps that Started in 2006, not Prior to so a slight amendment:

=IF((AND(A1>="1/1/2006"+0,B1>=100%)),"yes","no")
 

learningexcelnow

New Member
Joined
Oct 4, 2006
Messages
2
Ah yes, sorry...I had flipped the <> while playing around before posting...anyhow thanks for the help. By adding the " " around the date with+0 seemed to work fine!
 

Forum statistics

Threads
1,137,341
Messages
5,680,926
Members
419,945
Latest member
Carrie Sellers

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
Top