If Function to: Reject based on birthday

[jnix612]

I had to completely write this out because it got a little confusing and I'm trying to keep it simple for anyone who can help.

I am looking for IF function to auto reject based on BD:

• if true "Yes" If the BD falls 15-Jun-56 and before yes reject
• if false "No" If the BD falls 16-Jun-56 and after no do not reject

On the spreadsheet I have DOB in column "F" in the international format DD-MON-YR

The program rule is: note these are internationals and they use the international date format.

• If you turn 62 by the end of your assignment you are not qualified (end of assignment is 15-Jun-18)
  
• So if your BD is 15-Jun-56, you will be 62 on 15-Jun-18 - not qualified
• If you will turn 62 after the end of your assignment you are qualified
  
• So if your BD is 16-Jun-56, you will be 62 on 16-Jun-18 - qualified

I'm not good with dates in Excel anyway so I can't even come close to the If function for this.

[p45cal]

=IF(F2<DATEVALUE("15-Jun-1956"),"Yes","No")
or
=IF(F2<DATE(1956,6,15),"Yes","No")
or even
=IF(F2<20621,"Yes","No")
This assumes that the date in F2 is an Excel date. You can easily tell if this is the case by selecting the cell, right-clicking it and choosing Format Cells… then in the Number tab, click on General in the Category list, then take a look at what's in the Sample box; if it's a number like 20621, 43454, whatever, then it's a proper Excel date, if it appears still as a date then it's only text. At this stage, DON'T click OK or press Enter, just Escape out of the dialgue box or click Cancel, so that you don't change anything.

If it's text in cells in column F, come back.

[jnix612]

THANK YOU! THANK YOU!

[jnix612]

OK just saw your other note. I checked and it is in the date format in the sample box. I can use either of these. This is great. thank you.

[p45cal]

I checked and it is in the date format in the sample box.

!!??
So it looks like this when General is selected?:
Capture3.JPG
In that case the cells in column F contain only text and you will only get No as a result of the formula.
You will need to change the formula to:
=IF(DATEVALUE(F2)<20621,"Yes","No")
or
=IF(DATEVALUE(F2)<DATEVALUE("15-Jun-1956"),"Yes","No")
or
=IF(DATEVALUE(F2)<DATE(1956,6,15),"Yes","No")

[jnix612]

Under General, sample it shows 27759 and not the actual date, BUT I set this column as Custom, d-mmm-yy. I used your =IF(DATEVALUE(F2)<DATEVALUE("15-Jun-1956"),"Yes","No")

I like this if statement better because it would be clear to someone not familiar with Excel and If statements. If I leave the company I want them to see clearly what it means. Although I always add a worksheet explaining the formulas.

[mdmackillop]

You can also do a simple date check of =A1*1. If A1 is text, it will return an error.

[p45cal]

Under General, sample it shows 27759 and not the actual date,

Then it's an Excel date…

BUT I set this column as Custom, d-mmm-yy.

Once it's an Excel date it doesn't matter a jot to the formula how it's formatted, the format is only for the human eye.

I used your =IF(DATEVALUE(F2)<DATEVALUE("15-Jun-1956"),"Yes","No")

…then this will give a #VALUE! error. Revert to using one of the suggestions in msg#2.

[mdmackillop]

FYI
Excel just counts numbers: 01/01/1900 = 1 and dates are counted from there. Today is 42874.
Hours, minutes and seconds are fractions of days. At the time of posting Now() = 42874.8806930556