Need help with formula that includes dates

**kirkts** · 11-27-2012, 11:30 AM

I'm trying to create a formula that delivers the result: If the employee's start date is on or before 1/1/03 AND their birth date is on or before 1/1/58. From the data I provided in an example spreadsheet, I'm trying to identify just those employees with at least 10 years of experience as of 1/1/13 AND who will also be 55 years as of 1/1/13.

Hope someone can help. If not I will have to figure it out the old way by hand.

Thanks. Have a nice day!

**TheExcelphile** · 11-27-2012, 11:38 AM

Hi kirkts!
Welcome to the forum

Put the following formula in D2 in your sheet, and copy down:
=IF(AND(B2<=DATE(2003,1,1),C2<=DATE(1958,1,1)),"Meets Criteria","Doesn't Meet Criteria")

You can use whatever text you want in place of "Meets Criteria" and "Doesn't Meet Criteria"

Don't forget to click the little star at the bottom left of this post if you feel I helped!

**Pete_UK** · 11-27-2012, 11:41 AM

Put this formula in D2:

=IF(AND(B2<=--"01/01/2003",C2<=--"01/01/1958"),"Yes","No")

then copy down to the bottom of your data.

Hope this helps.

Pete

**kirkts** · 11-27-2012, 12:37 PM

Thank you both very much for your help! Worked like a charm!

Have a fabulous day!

**Steve N.** · 11-27-2012, 12:38 PM

Pete -

Can you explain the -- in the formula? First Iv'e seen that. Always trying to learn something new...

Steve

**TheExcelphile** · 11-27-2012, 02:11 PM

Hi Steve!

The -- is used when you want Excel to treat text in a formula as a number. In Pete's formula, he is comparing dates. If he kept "01/01/2003" as it is, Excel would treat it as text, not as a date. By using --, he is telling Excel to treat it as a number, because Excel stores dates as numbers.

Hope this makes sense

**kirkts** · 11-27-2012, 02:21 PM

Good question Steve! Thanks for asking. I was wondering the same thing.

**Pete_UK** · 11-27-2012, 02:51 PM

I did it that way, i.e. as text forced into a date with --, as it is a bit easier to read than DATE(year,month,day), but it does exactly the same thing.

Pete