Search

# Sumerian Voter Problem [IF formula homework]

### Can this person vote in Sumeria?

Imagine you are the chief election officer in the great country of Sumeria. You have introduced a new eligibility criteria for voters just before the grand presidential elections of 2016. In order to vote,

• Male citizens must be 21 years or older
• Female citizens must be 18 years or older
• Non-citizens can vote if they are 24 years or older and have been living in Sumeria since 1st of Jan 2006.
• Age should be calculated as of TODAY. For the purpose of age calculation, one year = 365 days.

So on one snowy April morning (it snows every month in Sumeria!), you find yourself staring at the Sumerian voter list. You need to find out if each person on that list can actually vote.

What formula would you write in the can vote? column.

Feel free to use below structural names or cell references in your answer.

• [@Gender] or C4
• [@[Date of Birth]] or D4
• [@Citizen] or E4
• [@[Resident Since]] or F4

Go ahead and figure out the formula

NOTE: When posting >= or > symbols in the formula, replace them with GTE or GT. Otherwise, wordpress (my blogging software) will eat up your > symbols.

Hello Awesome...

My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.

Thank you and see you around.

### Related articles:

 Written by Chandoo Tags: and(), downloads, Excel 101, homework, if() excel formula, Learn Excel, Microsoft Excel Formulas, OR() excel formula Home: Chandoo.org Main Page ? Doubt: Ask an Excel Question

### 135 Responses to “Sumerian Voter Problem [IF formula homework]”

1. Jake says:

I've gone for this:

=IF(E4="no",IF(AND(D4=DATEVALUE("01/01/2006")),"yes","no"),IF(OR(AND(C4="male",(D4<=(DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))-(21*365)))),AND(C4="female",(D4<=(DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))-(18*365))))),"yes","no"))

Seems to work, although I'm sure someone can work out something that’s less complicated!

• Jake says:

Correction, should be:

=IF(E4="no",IF(D4=DATEVALUE("01/01/2006"),"yes","no"),IF(OR(AND(C4="male",(D4<=(DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))-(21*365)))),AND(C4="female",(D4<=(DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))-(18*365))))),"yes","no"))

• TheQ47 says:

@Jake your formula doesn't take account of when non-citizens took up residence in the country. To fix that, you'd need to change the first part of your formula as follows:

=IF(E4="no",IF(F4<=DATEVALUE("01/01/2006"),...

However, even with this, I think your formula is still wrong. You need to ask if the non-citizen is over 24, which you haven't done. In other words, someone who entered the country on or before 1/1/2006 can vote, even if they're only 10 years of age!

2. gsvirdi says:

most raw formula which I can come up with is:
=IF([@Citizen]="Yes",IF(OR(AND([@Gender]="Male",TEXT(TEXT(TODAY()-[@[Date of Birth]],0)/365,0)>="21"),AND([@Gender]="Female",TEXT(TEXT(TODAY()-[@[Date of Birth]],0)/365,0)>="18")),"Yes","No"),IF(TEXT(TEXT(TODAY()-[@[Resident Since]], 0)/365, 0)>="24","Yes.","No"))

• TheQ47 says:

@gsvidri This formula doesn't work correctly. If someone is not a citizen, your formula only checks if they have been in the country more than 24 years. However it should check if they've been here since 1/1/2006 and if their date of birth is more than 24 years ago.

• TheQ47 says:

@gsvirdi I got your name wrong in the previous comment. Apologies.

3. James says:

=SUMPRODUCT(
(--([@Citizen]="Yes")*--([@Gender]="Male")*--(DATEDIF([@[Date of Birth]],TODAY(),"y")>=21))+
(--([@Citizen]="Yes")*--([@Gender]="Female")*--(DATEDIF([@[Date of Birth]],TODAY(),"y")>=18))+
(--([@Citizen]="No")*--(DATEDIF([@[Date of Birth]],TODAY(),"y")>=24)*--([@[Resident Since]]<=DATEVALUE("01/01/2006"))))

• Rob T says:

I went the SUMPRODUCT route too, and started off with (effectively) the same as James.
Then I reduced it down, eliminating duplication by moving the resident check and gender check to the DOB section, and the citizen check also to the Resident Since section (ensuring this section always returns TRUE for citizens).
The resulting formula is:
=IF(SUMPRODUCT(
(([@[Date of Birth]]<=(TODAY()-(
IF([@Citizen]="No",24,
IF([@Gender]="Male",21,18))*365))))
*([@[Resident Since]]<=
IF([@Citizen]="Yes",[@[Resident Since]],DATE(2006,1,1)))
)=0,"No","Yes")

If 1 or 0 are sufficient, can omit the outer IF(...=0,"No","Yes").

4. Amit Goyal says:

=IF([@Citizen]="Yes",OR(AND([@Gender]="Male",((TODAY()-[@[Date of Birth]])/365) GTE 21),AND([@Gender]="Female",((TODAY()-[@[Date of Birth]])/365) GTE 18)),(AND((TODAY()-[@[Date of Birth]])/365 GTE 24,[@[Resident Since]] GTE DATE(2006,1,1))))

5. Amit Goyal says:

Somehow the GTE did not convert to >=

• TheQ47 says:

@Amit Goyal I think your formula is almost correct, apart from at the very end, it should check if the [@[Resident Since]] is LTE Date(2006,1,1), i.e., before or on that date.

6. Grondmaster says:

=IF([@Citizen]="Yes",IF([@Gender]="Male",IF(((TODAY()-[@[Date of Birth]])/365)>20,"Yes","No"),IF(((TODAY()-[@[Date of Birth]])/365)>17,"Yes","No")),IF([@[Resident Since]]<NUMBERVALUE(DATE(2006,1,1)), IF(((TODAY()-[@[Date of Birth]])/365)>23,"Yes","No"),"No"))

using > & < instead of GT & GTE et al.

7. MF says:

=(DATEDIF([@[Date of Birth]],TODAY(),"Y")>=IF([@Gender]="Male",21,18))*([@Citizen]="Yes")+([@Citizen]="No")*([@[Resident Since]]=24

I did put an IF in my formula 🙂

• MF says:

Hey, wordpress did not eat up the ">="...

• MF says:

but it ate every thing in between .....
my formula should be
=(DATEDIF([@[Date of Birth]],TODAY(),"Y")GTE.IF([@Gender]="Male",21,18))*([@Citizen]="Yes")+([@Citizen]="No")*([@[Resident Since]]LTE.(DATE(2006,1,1)))*DATEDIF([@[Date of Birth]],TODAY(),"Y")GTE.24

where GTE. >=
LTE. <=

8. MikeB says:

=IF(AND([@Citizen]="YES",OR(AND([@Gender]="MALE",[@[Date of Birth]]<=TODAY()-(365*21)),AND([@Gender]="Female",[@[Date of Birth]]<=TODAY()-(365*18)))),1,IF(OR([@[Date of Birth]]<=TODAY()-(365*24),[@[Resident Since]]<=1/1/2006),1,0))

9. UG says:

=IF([@Citizen]="No",IF(AND(((TODAY()-[@[Date of Birth]])/365>24),[@[Resident Since]]21)),"Yes",IF(AND([@Gender]="Female",((TODAY()-[@[Date of Birth]])/36Usman5>18)),"Yes","No")))

10. UG says:

=IF([@Citizen]="No",IF(AND(((TODAY()-[@[Date of Birth]])/365>24),[@[Resident Since]]21)),"Yes",IF(AND([@Gender]="Female",((TODAY()-[@[Date of Birth]])/365>18)),"Yes","No")))

11. TheQ47 says:

=IF(INT((TODAY()-[@[Date of Birth]])/365) .GTE. 18,IF([@Citizen]="Yes",IF([@Gender]="female","VOTE",IF(INT((TODAY()-[@[Date of Birth]])/365) .GTE. 21,"VOTE","")),IF(INT((TODAY()-[@[Date of Birth]])/365) .GTE. 24,IF([@[Resident Since]]=18

Next, are you a citizen? If yes, are you female? is so, you can VOTE.
IF([@Citizen]="Yes",IF([@Gender]="female","VOTE"

If not female, then you're Male, but are you over 21? If so, you can VOTE, if not, No Vote.
IF(INT((TODAY()-[@[Date of Birth]])/365)>=21,"VOTE",""))

For those who aren't citizens, are you over 24?
IF(INT((TODAY()-[@[Date of Birth]])/365)>=24
If not, No Vote.

If you are over 24, have you lived here since 1/1/2006?
IF([@[Resident Since]]<=DATE(2006,1,1)
If so, you can VOTE.

Otherwise, No Vote.

• TheQ47 says:

Sorry, messed up on inserting that formula. Here it is again:

=IF(INT((TODAY()-[@[Date of Birth]])/365)>=18,IF([@Citizen]="Yes",IF([@Gender]="female","VOTE",IF(INT((TODAY()-[@[Date of Birth]])/365)>=21,"VOTE","")),IF(INT((TODAY()-[@[Date of Birth]])/365)>=24,IF([@[Resident Since]]=18

Next, are you a citizen? If yes, are you female? is so, you can VOTE.
IF([@Citizen]="Yes",IF([@Gender]="female","VOTE"

If not female, then you're Male, but are you over 21? If so, you can VOTE, if not, No Vote.
IF(INT((TODAY()-[@[Date of Birth]])/365)>=21,"VOTE",""))

For those who aren't citizens, are you over 24?
IF(INT((TODAY()-[@[Date of Birth]])/365)>=24
If not, No Vote.

If you are over 24, have you lived here since 1/1/2006?
IF([@[Resident Since]]<=DATE(2006,1,1)
If so, you can VOTE.

Otherwise, No Vote.

12. TheQ47 says:

Third time lucky, I don't know what's happening here, but if this doesn't work, I'm giving up:

=IF(INT((TODAY()-[@[Date of Birth]])/365) .GTE. =18,IF([@Citizen]="Yes",IF([@Gender]="female","VOTE",IF(INT((TODAY()-[@[Date of Birth]])/365) .GTE. 21,"VOTE","")),IF(INT((TODAY()-[@[Date of Birth]])/365) .GTE. 24,IF([@[Resident Since]]<=DATE(2006,1,1),"VOTE",""),"")),"")

13. Michael (Micky) Avidan says:

My suggested formula resides within the linked picture.

http://s31.postimg.org/jbbfr9juj/NONAME.png

=IF(OR((E4="No")*(TODAY()-D4 GTE 24*365)*(F4 STE --"1/1/2006"),((C4="Male")*(TODAY()-D4 GTE 21*365))+((C4 NOT EQUAL "Male")*TODAY()-D4>=18*365)),"Yes","No")

Michael Avidan
ISRAEL

• SunnyKow says:

Hi Michael
Your formula does not seems to work properly for non-citizen. I suspect it is the RESIDENT SINCE date calculation where you put the double negative to force the text date 1/1/2006 to a value for comparison.
Eg.
Voter ID Citizen Resident Since Can vote?
SV-000.041 No 4 Apr / 2006 Yes
SV-000.086 No 17 Apr / 2008 Yes
SV-000.106 No 12 Oct / 2010 Yes
SV-000.126 No 29 Jul / 2007 Yes

14. This looks good and works on the demo excel sheet.. Please check & confirm.. Thank you for this Home work, Chandoo 🙂

=IF([@Citizen]="Yes",IF(OR(AND([@Gender]="Male",(TODAY()-[@[Date of Birth]])/365 >= 21),(AND([@Gender]="Female",(TODAY()-[@[Date of Birth]])/365 >= 18))),"Yes","No"),IF(AND((TODAY()-[@[Date of Birth]])/365 >= 24, ([@[Resident Since]]<= DATE(2006,1,1))),"Yes","No"))

15. Steve Stafford says:

Used the R1C1 cell reference to create a Formula > Name “ageyears”. “Ageyears” referred to:
=DATEDIF(data!RC[-3],TODAY(),"y").

In the “Can Vote?” column, then used this formula: =IF(OR(AND([@Citizen]="yes",[@Gender]="male",ageyears>=21),AND([@Citizen]="yes",[@Gender]="female",ageyears>=18),AND([@Citizen]="no",[@[Resident Since]]=24)),"Yes","No")

• Steven Stafford says:

Not sure why but when formula was copied and pasted a key element was left out. Here is revised formula;

Used the R1C1 cell reference to create a Formula > Name “ageyears”. “Ageyears” referred to:
=DATEDIF(data!RC[-3],TODAY(),"y").

In the “Can Vote?” column, then used this formula: =IF(OR(AND([@Citizen]="yes",[@Gender]="male",ageyears>=21),AND([@Citizen]="yes",[@Gender]="female",ageyears>=18),AND([@Citizen]="no",[@[Resident Since]]=24)),"Yes","No"

• Steven Stafford says:

Unsure why but when I post, even though it appears correct in the message area, there is a piece of the final "AND" clause that is left out. This clause looks to see if the resident was there prior to 1/1/06 (I assumed that it was inclusive of 1/1/06).

The final "AND" clause should read;

"AND([@Citizen]="no",[@[Resident Since]]=24)"

Ok this looks right in the message window, Now I'll submit.

• Steven Stafford says:

Didn't work again, but think you get idea.

• hbillions says:

Now i understand Steve. Happened to me too; twice!

16. Steven Stafford says:

Used the R1C1 cell reference to create a Formula > Name “ageyears”. “Ageyears” referred to:
=DATEDIF(data!RC[-3],TODAY(),"y").

In the “Can Vote?” column, then used this formula: =IF(OR(AND([@Citizen]="yes",[@Gender]="male",ageyears>=21),AND([@Citizen]="yes",[@Gender]="female",ageyears>=18),AND([@Citizen]="no",[@[Resident Since]]=24)),"Yes","No")

17. Elias says:

D2 = TODAY()
F2 = 1/1/2016
G2 = Male

=IF(E4="No",F4>\$F\$2,(\$D\$2-D4)/365>IF(C4=\$G\$2,20,17))

Regards

• Michael (Micky) Avidan says:

@Elias,
Assuming you meant 1/1/2006 in cell F2 (not 1/1/2016) please check voter ID. SV-000.001
He is a MALE, a CITIZEN and "only" 20.15 Years old.
Your suggested formula returns TRUE for him.

Voter ID: SV-000.011 in NOT a Citizen but she is "only" 12.31 Years old.
Your suggested formula returns TRUE for her.
--------------------------------------
Michael Avidan
ISRAEL

• Elias says:

Fixed

=IF(E4="No",AND(F4+1>\$F\$2,(\$D\$2-D4)/365 GTE 24),(\$D\$2-D4)/365 GTE IF(C4=\$G\$2,21,18))

Elias

• Michael (Micky) Avidan says:

@Elias,
Your current formula also returns TRUE for the voter ID. SV-000.001 who did not reach the age of 21 yet.
Assuming my suggested formula returns the expected results - please check/compare at leat 200 Voters to be sure your formula returns the same.
--------------------------------------
Michael Avidan
ISRAEL

• Elias says:

@Michael

For that particular voter and all the Citizens my formula is returning the correct results. However, it needs a little change to work correctly with No citizens.

=IF(E44="No",AND(F44 LTE \$F\$2,(\$D\$2-D44)/365 GTE 24),(\$D\$2-D44)/365 GTE IF(C44=\$G\$2,21,18))

Also, you may want to review your formula because it is returning wrong results for No citizens

Regards

18. Wanderlei Santos says:

=AND(((TODAY()-[@[Date of Birth]])/365) GTE IF([@Citizen]="Yes",IF([@Gender]="Male",21,18),24),OR([@Citizen]="Yes",AND([@[Resident Since]] NE "",[@[Resident Since]] LTE DATE(2006,1,1))))

19. Wanderlei Santos says:

new simpler formula:
=AND(((TODAY()-[@[Date of Birth]])/365)>=IF([@Citizen]="Yes",IF([@Gender]="Male",21,18),24),OR([@Citizen]="Yes",[@[Resident Since]]<=DATE(2006,1,1)))

20. Mike Marshall says:

=IF(OR(
AND([@Citizen]="No",(TODAY()-[@[Date of Birth]])/365 GTE 24,[@[Resident Since]] GTE DATE(2006,1,1)),
AND([@Citizen]="Yes",[@Gender]="Male",(TODAY()-[@[Date of Birth]])/365 GTE 21),
AND([@Citizen]="Yes",[@Gender]="Female",(TODAY()-[@[Date of Birth]])/365 GTE 18)),
"Yes","No")

21. Mia Munn says:

With reason if can't vote (MC<18, FC<18, NC21*365,"Y","MC18*365,"Y","FC24*365,"Y","NC<24"))

• Mia Munn says:

Forgot the Non Citizen resident clause (and forgot to convert > to GT:

=IF(Citizen="Yes",IF(Gender="Male",IF(TODAY()-[@[Date of Birth]] GT 21*365,"Y","MC<21"),IF(TODAY()-[@[Date of Birth]] GT 18*365,"Y","FC<18")),IF(TODAY()-[@[Date of Birth]] GT 24*365,IF([@[Resident Since]]-42370 GTE 0,"Y","NCNR"),"NC<24"))

22. Ellaysee says:

So many ways to get there! Here's mine...

=IF(OR(AND([@Citizen]="yes",[@Gender]="male",(TODAY()-[@[Date of Birth]])/365 GTE 21),AND([@Citizen]="yes",[@Gender]="female",(TODAY()-[@[Date of Birth]])/365 GTE 18),AND([@Citizen]="no",(TODAY()-[@[Date of Birth]])/365 GTE 24,[@[Resident Since]] LTE DATE(2006,1,1))),"Yes","No")

23. Ashish Dabral says:

=IF(AND(voters[[#This Row],[Gender]]="Male",DATEDIF(voters[[#This Row],[Date of Birth]],TODAY(),"Y")>=21,voters[[#This Row],[Citizen]]="Yes"),"Yes",IF(AND(voters[[#This Row],[Gender]]="Female",DATEDIF(voters[[#This Row],[Date of Birth]],TODAY(),"Y")>=18,voters[[#This Row],[Citizen]]="Yes"),"Yes",IF(AND(voters[[#This Row],[Resident Since]]<=DATE(2006,1,1),voters[[#This Row],[Resident Since]]"",DATEDIF(voters[[#This Row],[Date of Birth]],TODAY(),"Y")>=24),"Yes","No")))

24. hbillions says:

Here's my first attempt below.

=IF(AND(Gender&Citizen="MaleYes",DATEDIF(Date_of_Birth,TODAY(),"y")>=21),"YES",IF(AND(Gender&Citizen="FemaleYes",DATEDIF(Date_of_Birth,TODAY(),"y")>=18),"YES",IF(AND(Citizen="No",Resident_Since=24),"YES","NO")))

Too bad shorter and simpler formulas already proposed? Working to shorten it.

25. Nagaraj says:

=OR(AND([@Gender]="Male",[@Citizen]="Yes",INT(TODAY()-[@[Date of Birth]])/365>=21),AND([@Gender]="Female",[@Citizen]="Yes",INT(TODAY()-[@[Date of Birth]])/365>=18),AND([@Citizen]="No",[@[Resident Since]]=24))

26. hbillions says:

A portion of the last IF statement was cut-off for some reasons. Here's the full portion of the last IF statement:

...IF(AND(Citizen="No",Resident_Since=24),"YES","NO")))

27. Nagaraj says:

Previous one was an error.

=OR(AND([@Gender]="Male",[@Citizen]="Yes",INT(TODAY()-[@[Date of Birth]])/365>=21),AND([@Gender]="Female",[@Citizen]="Yes",INT(TODAY()-[@[Date of Birth]])/365>=18),AND([@Citizen]="No",[@[Resident Since]]=24))

28. Steve says:

=IF(SUMPRODUCT((IF([@Citizen]="Yes",1,IF([@[Resident Since]]>=38718,1,0)))*(IF(AND([@Gender]="Female",((TODAY()-[@[Date of Birth]])/365)>18),1,IF( (TODAY()-[@[Date of Birth]])/365>21,1,0)))),"Yes","No")

29. Maciej says:

The simplest approach is with nested IFs:
=IF([@Citizen]="Yes";IF([@Gender]="Male";DATEDIF([@[Date of Birth]];TODAY();"y") GT 20;DATEDIF([@[Date of Birth]];TODAY();"y") GT 17);AND(DATEDIF([@[Date of Birth]];TODAY();"y") GT 23;[@[Resident Since]] LE DATE(2016;1;1)))

Here is formula without IFs:
=CHOOSE(LEN([@Citizen]);;AND(DATEDIF([@[Date of Birth]];TODAY();"y") GT 23;[@[Resident Since]] LE DATE(2016;1;1));CHOOSE(LEN([@Gender])/2;;DATEDIF([@[Date of Birth]];TODAY();"y") GT 20;DATEDIF([@[Date of Birth]];TODAY();"y") GT 17))

My solution is user-defined excel function as follows:
=CanVote(B4,C4,D4,E4,F4)

where CanVote is bolean type private funtion

Private Function CanVote (x as bolean)
.
.
.
End Function

31. Ted says:

This answer tells the reason why (or why not) the person can vote, rather than just yes/no. It could be used for statistics. 🙂

=IF([@Citizen]="Yes", IF([@Gender]="Male", IF((TODAY()-[@[Date of Birth]])/365 GTE 21,"Yes- male citizen can vote","No- male citizen too

young"), IF([@Gender]="Female", IF((TODAY()-[@[Date of Birth]])/365 GTE 18, "Yes- woman citizen ok to vote","No- woman citizen too

young"))), IF([@Citizen]="No", IF((TODAY()-[@[Date of Birth]])/365 LTE 24, "No- non-citizen too young", IF([@[Resident Since]] LT DATE

(2006,1,1), "Yes- non-citizen meets requirements to vote", "No- non-citizen hasn't been resident long enough"))))

32. Ted says:

Those crazy Sumerians. In the sample data file, 25 had Residency before they were born.

• Chandoo says:

Good find. I blame the Excel's random data generators 😉

• Michael (Micky) Avidan says:

@Chandoo,
For my bad English knowledge (and it has onlt to do with GTE or STE) - a NON Citizen will be allowed to vote if he/she are 24 years or older and have been living in Sumeria since 1st. of Jan, 2006.
Does this mean that if one is 26 years old and was living in Sumeria on the 25th, Dec. 2005 he/she will not be allowed - or one should have been living since 1st. Jan, 2006 up to Today BUT NOT BEFORE the 1st. Jan, 2006 ?
Thanks,
Michael Avidan
ISRAEL

• Chandoo says:

In this case, the non citizen can vote.

• Michael (Micky) Avidan says:

Sorry but I still don't fully understand.
Please refer to the voters in rows: 44, 49 snd 52.
Which one, of them, can vote and who cannot ?
Thanks,
Michael Avidan
ISRAEL

• Mehmet Gunal OLCER says:

Chandoo,

please do not blame the RNG. You could use a formula something like,

RANDBETWEEN([@[Date of Birth]],TODAY())

• Chandoo says:

Of course, the blame is with me. Excel simply produced data based on the formulas I wrote 🙂

33. sagar malik says:

OR(AND([@Citizen]="Yes",OR(AND([@Gender]="Male",TODAY()-[@[Date of Birth]] GTE 21*365),AND([@Gender]="Female",TODAY()-[@[Date of Birth]] GTE 18*365))),AND([@Citizen]="No",TODAY()-[@[Date of Birth]] GTE 24*365,NOT(ISBLANK([@[Resident Since]])),[@[Resident Since]] LTE DATE(2006,1,1)))

34. martinjust says:

=IF(SUM(SUMPRODUCT(([@Citizen]="Yes")*([@Gender]="Male")*(INT((TODAY()-[@[Date of Birth]])/365)>=21)),SUMPRODUCT(([@Citizen]="Yes")*([@Gender]="Female")*(INT((TODAY()-[@[Date of Birth]])/365)>=18)),SUMPRODUCT(([@Citizen]="No")*([@[Resident Since]]>=DATE(2006,1,1)))*([@age]>=24)),"Yes","No")

35. David N says:

=IF(E4="Yes",IF(C4="Male",21,18),IF(F4<=DATE(2006,1,1),24,999))<=(TODAY()-D4)/365

• Michael (Micky) Avidan says:

@David N,
From the few voters on whom I checked your suggested formula I can only say: "Chapeau bas vous !!!"
Michael Avidan
ISRAEL

• Elias says:

@ David,

I like this one!

Regards

36. RAC says:

=IF(AND((voters[[#This Row],[Gender]]="Male"),(H4>=21),(voters[[#This Row],[Citizen]]="Yes")),"Yes",IF(AND((voters[[#This Row],[Gender]]="Female"),(H4>=18),(voters[[#This Row],[Citizen]]="Yes")),"Yes",IF(AND((H4>=24),(voters[[#This Row],[Citizen]]="No"),(I4>=3765)),"Yes","No")))

H4 : =DATEDIF(voters[[#This Row],[Date of Birth]],TODAY(),"y")

I4: =IFERROR(DATEDIF(voters[[#This Row],[Resident Since]],TODAY(),"d"),0)

37. Suryakant Kulkarni says:

=IF([@Citizen]="Yes",IF(AND([@Gender]="Male",YEAR(TODAY())-YEAR([@[Date of Birth]])>21),"Yes",IF(AND([@Gender]="Female",YEAR(TODAY())-YEAR([@[Date of Birth]])>18),"Yes","No")),IF(AND(YEAR(TODAY())-YEAR([@[Date of Birth]])>21,[@[Resident Since]]<DATE(2006,1,1)),"Yes","No"))

38. Suryakant Kulkarni says:

Hi
Chandoo,

Just small change in formula,
=IF([@Citizen]="Yes",IF(AND([@Gender]="Male",((TODAY()-[@[Date of Birth]])/365)>21),"Yes",IF(AND([@Gender]="Female",((TODAY()-[@[Date of Birth]])/365)>18),"Yes","No")),IF(AND(((TODAY()-[@[Date of Birth]])/365)>21,[@[Resident Since]]<DATE(2006,1,1)),"Yes","No"))

39. Mehmet Gunal OLCER says:

=OR(AND(([@Gender]="Male"),(TODAY()-[@[Date of Birth]] GT 365*21),[@Citizen]="Yes"),AND(([@Gender]="Female"),(TODAY()-[@[Date of Birth]] GT 365*18),[@Citizen]="Yes"),AND((TODAY()-[@[Date of Birth]] GT 365*24),[@Citizen]="No"))

• Mehmet Gunal OLCER says:

Hi Chandoo,

=OR(AND(([@Gender]="Male"),(TODAY()-[@[Date of Birth]] GT 365*21),[@Citizen]="Yes"),AND(([@Gender]="Female"),(TODAY()-[@[Date of Birth]] GT 365*18),[@Citizen]="Yes"),AND((TODAY()-[@[Date of Birth]] GT 365*24),[Resident Since] LT 38718))

40. Chandra Mohan Singh says:

IF(AND(D12="No",VALUE((TODAY()-C12)/365)>=24,E12>=1/1/2006),"Yes",IF(AND(D12="Yes",B12="Male",VALUE((TODAY()-C12)/365)>=21),"Yes",IF(AND(D12="Yes",B12="Female",VALUE((TODAY()-C12)/365)>=18),"Yes","No")))

41. Gurminder Singh Puri says:

The formula to solve the problem is as under :

=IF(OR(AND(@Gender="M",(TODAY()-@Date Of Birth)/365 GTE 21),AND(@Gender="F",(TODAY()-@Date Of Birth)/365 GTE 18),AND(@Resident Since GTE "01-01-2006",(TODAY()-@Date Of Birth)/365 GTE 24)),"Yes","No")

I am not a big expert, but I have tried to solve the problem. GTE is used for Greater than or equal to and GT is used for Greater Than as advised by you

42. Gurminder Singh Puri says:

I have noticed an error in the formula sent by me. Am working on it and will resend after correction. Kindly Excuse.

43. Phil says:

Hi Chandoo,

nice challenge which very much reminded me of Daniel Ferry's amazing post about I HEART IF.

I wanted to avoid nested IFs by any mean so I chose his approach by applying the numerical values of 0 and 1 for FALSE and TRUE and simply add up all three preconditional stages (male citizen, female citizen, non-citizen)

Here are the steps I took:

To apply readability to the formulas used I first created a few names. First set was the "variables" for the ages.

1. sumeria.age18 has been defined as =18*365
2. sumeria.age21 has been defined as =21*365
3. sumeria.age24 has been defined as =24*365

I could have used the absolute numbers as well but this helps the future use of this file a little more, because these definitions (CTRL+F3 for the name manager) help better understand what I was doing

Then I placed the cursor in the first row (4) and again hit CTRL+F3 to define another variable for "todays age in days":

4. age.in.days has been defined as =TODAY()-\$D4

Now that I have these "readability issues" solved, it's time to create the formula itself. Here's how I approached it:

As several conditions need to be matched I applied AND - as my fellow Excel buddies did - for each of the three condition sets and added them up. I first checked for male citizens, then female citizens and then non-citizens

=AND([@Gender]="Male",age.in.days GTE sumeria.age21,[@Citizen]="Yes")+AND([@Gender]="Female",age.in.days GTE sumeria.age18,[@Citizen]="Yes")+AND([@Citizen]="no",age.in.days>=sumeria.age24,[@[Resident Since]]=sumeria.age24,[@[Resident Since]]<="01.01.2006")

Remarks: One could expand the naming part even further by applying names for the other checks - such as defining is.male as =[@Gender]="Male" - as well but that was a little "over the top" for me 😉

Hope you like my solution 🙂

• Phil says:

Looks like WOrdpress has cut down some of my content, because I also added a breakdown of the evaluation for each condition set, so here's that missing part 😉

Here are the three parts broken down for each condition set:

MALE CITIZENS (first part, initiated by the = sign):

=AND([@Gender]="Male",age.in.days GTE sumeria.age21,[@Citizen]="Yes")

FEMALE CITIZENS (second part, initiated by the + sign):
+AND([@Gender]="Female",age.in.days GTE sumeria.age18,[@Citizen]="Yes")

NON-CITIZENS (third part, initiated by another + sign):

AND([@Citizen]="no",age.in.days>=sumeria.age24,[@[Resident Since]] LTE "01.01.2006")

44. Stewart says:

I went with a few bits to say WHY they couldn't vote...

IF([@[Resident Since]]="",
IF([@Gender]="male",
IF(DATEDIF([@[Date of Birth]],TODAY(),"y") GTE 21,"Yes","No (male, too young)"),
IF([@Gender]="Female",IF(DATEDIF([@[Date of Birth]],TODAY(),"y") GTE 18,"Yes","No (female, too young)"))),
IF([@Citizen]="No",
IF([@[Resident Since]]<="1/1/2006",
IF(DATEDIF([@[Date of Birth]],TODAY(),"y") GTE 24,"Yes","No (non-citizen, too young)"),
"No (non-citizen, residency too short)"),
"No (not citizen and/or failed other rules)"))

45. Elias says:

One more without IFs
D2 = TODAY()
F2 = 1/1/2006
G2 = Male

=AND((\$D\$2-D4)/365 GTE MAX(24*(E4="No"),21*(E4="Yes")*(C4=\$G\$2),18*(E4="Yes")*(C4\$G\$2)),(E4="Yes")+(F4 LTE \$F\$2))

Regards

• Michael (Micky) Avidan says:

@Elias,
1) I know my formula is not perfect and in fact I gave up and stopped altering it after I saw David,s formula.
-----------------------------------------------------
SV-000.043 Female 01/09/2001 Yes
-----------------------------------------------------
She is only 14.65 years old, therefore the result should read: FALSE.

Michael Avidan
ISRAEL

• Elias says:

@Michael

I don't know how you are applying my formulas because my last formula and the one I replied to you in a previous comments return FALSE for that voter.

Regards

• Michael (Micky) Avidan says:

@Elias,
As there is a limit of replies in each post - with your permission after this reply, of mine, I will rest my case.
I do hope I didn't made any mistake in copying your formula nor changing the GTE respectively.
Here is a picture of row 46 voter + your formula + result (shoud be FALSE).
---------------------------------------------------------
http://s31.postimg.org/facmsw123/NONAME.png
---------------------------------------------------------
Michael Avidan
ISRAEL

• Michael (Micky) Avidan says:

@Elias,
Check out the linked picture - where in row 7589 the result should be FALSE because the voter residency started on: 02/07/2001 which is prior to: 1/1/2006.
------------------------------------------------------
http://s31.postimg.org/cjht2qgwr/NONAME.png
------------------------------------------------------
Michael Avidan
ISRAEL

46. Elias says:

@Michael,

Sorry you were right. My last formula without IF was not correct. Here is the new version without IF, OR & AND.

((E4="No")*24+(E4="Yes")*(21*(C4=\$G\$2)+18*(C4\$G\$2)))*365<=(\$D\$2-D4)*((E4="Yes")+(E4="No")*(F4<=\$F\$2))

Regards

47. Praveen Bharadwaj K R says:

Formula will be as follows

IF([@Gender]="Male",IF(AND([@Citizen]="Yes",[@Age]GTE21),"Yes",IF(AND([@Citizen]="No",[@Age]GTE24,[@[Resident Since]]LTEDATE(2006,1,1)),"Yes","No")),IF(AND([@Citizen]="Yes",[@Age]GTE18),"Yes",IF(AND([@Citizen]="No",[@Age]GTE24,[@[Resident Since]]GTEDATE(2006,1,1)),"Yes","No")))

Note: GTE Greater Than or Equal to & LTE Less Than or Equal to

• Praveen Bharadwaj K R says:

For Age Calculation I have used following Formula:

INT(YEARFRAC([@[Date of Birth]],TODAY()))

48. Michael (Micky) Avidan says:

@Elias,
With all due respect this is my last check/response as for your formula(s).
I hope Chandoo will check all the suggestions and reply accordingly.
Your last formula returns FALSE for Voter: SV-000.004 although he is a Male & a Citizen & Older than 24 years.
To my opinion - an Equal sign (=) is missing within the: C4\$G\$2 section.
I have added it in order for the formula to work.
Kind regards,
Michael Avidan
ISRAEL

• Elias says:

@Michael, I didn't notice the missing sign. It is a different than . Also, my formula returns exactly the same results as David N formula for all the records.

Regards

49. Jude Shyju says:

OPTION 1

=IF(E11="Sumeria",IF(OR(AND(C11="Male",TODAY()-D11 GT 365*21),AND(C11="Female",TODAY()-D11 GT 365*18)),"Eligible","Not Eligible"),IF(AND(TODAY()-D11 GT 365*24,F11 LT DATE(2006,1,1)),"Eligible","Not Eligible"))

OPTION 2 (Using SUMPRODUCT)

=IF(OR(SUMPRODUCT((C10="Male")*(E10="Sumeria")*(TODAY()-D10 GT 365*21)),SUMPRODUCT((C10="Female")*(E10="Sumeria")*(TODAY()-D7 GT 365*18)),SUMPRODUCT(((E10LTGT"Sumeria")*(TODAY()-D10 GT 365*24)*(F10LTDATE(2006,1,1))))),"Eligible","Not Eligible")

50. Jayant says:

=(IF(AND(C4="Male",DATEDIF(D4,TODAY(),"Y")>=21),"Y",(IF(AND(C4="Female",DATEDIF(D4,TODAY(),"Y")>=18),"Y",IF(E4="No",IF((DATEDIF(D4,TODAY(),"Y")>=24),"Y","N"),"N")))))

51. Jason Morin says:

=(((TODAY()-D4)/365GTE21)*(C4="Male")+((TODAY()-D4)/365GTE18)*(C4="Female"))*(E4="Yes")+((TODAY()-D4)/365GTE24)*(F4<=DATE(2006,1,1))

52. SunnyKow says:

=IF(F4="",IF(OR(AND(C4="Male",(TODAY()-D4)/365GE21),AND(C4="Female",(TODAY()-D4)/365GE18)),"Yes","No"),IF(AND(F4LE38718,(TODAY()-D4)/365GE24),"Yes","No"))

53. Ashish says:

My suggested solution:
=+IF(OR(AND([@Citizen]="Yes",[@Gender]="Male",DATEDIF([@[Date of Birth]],TODAY(),"y")GTE21),AND([@Citizen]="yes",[@Gender]="Female",DATEDIF([@[Date of Birth]],TODAY(),"y")GTE18),AND([@Citizen]="No",DATEDIF([@[Date of Birth]],TODAY(),"y")GTE24,[@[Resident Since]]LTEDATE(2006,1,1))),"Yes","No")

54. Kapil says:

=IF((--(voters[@Citizen]="Yes")*(--(OR(AND(voters[@Gender]="male",((TODAY()-voters[@[Date of Birth]])/365) GT21),AND(voters[@Gender]="Female",((TODAY()-voters[@[Date of Birth]])/365) GT24)))))+(--(voters[@Citizen]="No")*(--(AND("01-01-2006"GTvoters[@[Resident Since]],((TODAY()-voters[@[Date of Birth]])/365)>21))))=1,"Yes","No")

55. Basan0187 says:

IF(AND(E4="Yes",C4="Male",((TODAY()-D4)/365)>=21),"Y",IF(AND(E4="Yes",C4="feMale",((TODAY()-D4)/365)>=18),"Y",IF(AND(E4="No",((TODAY()-D4)/365)>=24,F4>=1/1/2006),"Y","N")))

56. Cyril says:

Suggested solution:
=IF([@Citizen]="Yes",IF([@Gender]="Male",IF((TODAY()-[@[Date of Birth]])/365GTE21,"Yes","No"),IF((TODAY()-[@[Date of Birth]])/365GTE18,"Yes","No")),IF([@[Resident Since]]GTE38718,"No",IF((TODAY()-[@[Date of Birth]])/365GTE24,"Yes","No")))
or:
=IF(OR(AND([@Citizen]="Yes",[@Gender]="Male",(TODAY()-[@[Date of Birth]])/365GTE21),AND([@Citizen]="Yes",[@Gender]="Female",(TODAY()-[@[Date of Birth]])/365GTE18),AND([@Citizen]="No",[@[Resident Since]]<=38718,(TODAY()-[@[Date of Birth]])/365GTE24)),"Yes","No")

57. Eumelode says:

=IF(@Citizen="No",
IF(@Residentsince>DATE(2006,1,1),"No Foreign Young",IF(TODAY()-@0,"Yes","No"))

58. ANKUSH says:

=IF(voters[[#This Row],[Citizen]]="Yes",OR(AND(voters[[#This Row],[Gender]]="Male",((TODAY()-voters[[#This Row],[Date of Birth]])/365)>21),AND(voters[[#This Row],[Gender]]="Female",((TODAY()-voters[[#This Row],[Date of Birth]])/365)>18)),OR(((TODAY()-voters[[#This Row],[Date of Birth]])/365)>=24,voters[[#This Row],[Resident Since]]<DATE(2006,1,1)))

59. Alex Groberman says:

=AND(OR([@Citizen]="Yes",[@[Resident Since]]<=DATE(2006,1,1)),YEARFRAC([@[Date of Birth]],TODAY(),3)>=IF([@Citizen]="No",24,IF([@Gender]="Male",21,18)))

60. Ben Oshyer says:

=IF(((E4="yes")*((C4="Male")*(((TODAY()-D4)/365)>21))+((C4="Female")*(((TODAY()-D4)/365)>18)))+((E4="No")*(((TODAY()-D4)/365)>24)*(AND(F4"",F4>DATE(2006,1,1))))>0,"Yes","No")

61. Marydas says:

=IF(AND(data!\$E4="Yes",data!\$C4="Male",YEARFRAC(data!\$D4,TODAY(),3)>=21),"Can Vote",IF(AND(data!\$E4="Yes",data!\$C4="Female",YEARFRAC(data!\$D4,TODAY(),3)>=18),"Can Vote",IF(AND(E4="No",YEARFRAC(D4,TODAY(),3)>=24,F4<38718),"Can Vote",0)))

62. Ash says:

Hi Chandoo,

Here's the formula I came up with:

=IF([@Citizen]="Yes",IF(AND([@Gender]="Male",((TODAY()-[@[Date of Birth]])/365)>=21),"Yes",IF(AND([@Gender]="Female",((TODAY()-[@[Date of Birth]])/365)>=18),"Yes","No")),IF(AND((TODAY()-[@[Date of Birth]])/365>=24,[@[Resident Since]]<=DATE(2006,1,1)),"Yes","No"))

63. Daniel H says:

=OR(AND([@[Resident Since]]>DATE(2015,12,31),DATEDIF([@[Date of Birth]],TODAY(),"y")>23),AND([@[Resident Since]]="",DATEDIF([@[Date of Birth]],TODAY(),"y")>17+N([@Gender]="Male")*3))

64. Miguel Cubeles says:

I did it, took me a while, I liked this one, 🙂

=IF(AND(voters[@Gender]="male",INT(TEXT(TODAY()-voters[@[Date of Birth]],"yy"))>21,voters[@Citizen]="Yes"),"YES",IF(AND(voters[@Gender]="Female",INT(TEXT(TODAY()-voters[@[Date of Birth]],"yy"))>18,voters[@Citizen]="Yes"),"YES",IF(AND(voters[@Citizen]="No",voters[@[Resident Since]]>DATE(2006,1,1)),"YES","NO")))

65. Miguel Cubeles says:

forgot to put =
there it is
=IF(AND([@Gender]="male",INT(TEXT(TODAY()-[@[Date of Birth]],"yy"))>=21,[@Citizen]="Yes"),"YES",IF(AND([@Gender]="Female",INT(TEXT(TODAY()-[@[Date of Birth]],"yy"))>=18,[@Citizen]="Yes"),"YES",IF(AND([@Citizen]="No",[@[Resident Since]]>=DATE(2006,1,1)),"YES","NO")))

66. Miguel Cubeles says:

=IF(AND([@Gender]="male",INT(TEXT(TODAY()-[@[Date of Birth]],"yy"))>=21,[@Citizen]="Yes"),"CAN VOTE",IF(AND([@Gender]="Female",INT(TEXT(TODAY()-[@[Date of Birth]],"yy"))>=18,[@Citizen]="Yes"),"CAN VOTE",IF(AND([@Citizen]="No",INT(TEXT(TODAY()-[@[Date of Birth]],"yy"))>=24,[@[Resident Since]]>=DATE(2006,1,1)),"CAN'T VOTE","CAN'T VOTE")))

67. Chirayu says:

=IF(AND(C88="Male",E88="Yes",((TODAY()-D88)/365)>=21),"Yes",IF(AND(C88="Female",E88="Yes",((TODAY()-D88)/365)>=18),"Yes",IF(AND(E88="No",((TODAY()-D88)/365)>=24,F88>=DATE(2006,1,1)),"Yes","No")))

• Chirayu says:

I accidentally took the formula from row 88 lol

68. Mike B says:

=IF(OR(AND(((TODAY()-TEXT([@[Date of Birth]],"mm/dd/yyyy"))/365)>=21,[@Gender]="Male",[@Citizen]="Yes"),(AND(((TODAY()-TEXT([@[Date of Birth]],"mm/dd/yyyy"))/365)>=18,[@Gender]="Female",[@Citizen]="Yes"))),"Yes",IF(AND([@Citizen]="No",TEXT([@[Resident Since]],"mm/dd/YYYY")>1/1/2006,((TODAY()-TEXT([@[Date of Birth]],"mm/dd/yyyy"))/365)>=24),"Yes","No"))

• Mike B says:

114 Female and 127 Male Non-Citizens Cannot vote
361 Female and 657 Male Citizens Cannot vote
227 Female and 255 Male Non-Citizens CAN vote
3050 Female and 2746 Male Citizens CAN vote

69. Mike B says:

Update-Did not handle DOB correctly for Citizenry
=IF(OR(AND(((TODAY()-TEXT([@[Date of Birth]],"mm/dd/yyyy"))/365)>=21,[@Gender]="Male",[@Citizen]="Yes"),(AND(((TODAY()-TEXT([@[Date of Birth]],"mm/dd/yyyy"))/365)>=18,[@Gender]="Female",[@Citizen]="Yes"))),"Yes",IF(AND((TODAY()-(TEXT([@[Date of Birth]],"mm/dd/yyyy")))/365>24,DATE(2006,1,1)>=[@[Resident Since]]FALSE),"Yes","No"))

70. Jacques L says:

=IF(OR(AND([@Gender]="Male",ROUNDDOWN((TODAY()-[@[Date of Birth]])/365,0)GTE21,[@Citizen]="Yes"),AND([@Gender]="Female",ROUNDDOWN((TODAY()-[@[Date of Birth]])/365,0)GTE18,[@Citizen]="Yes"),AND([@Citizen]="No",ROUNDDOWN((TODAY()-[@[Date of Birth]])/365,0)GTE24,38718GTE[@[Resident Since]]))=TRUE,"YES","No")

I see most of the post did not include the "GTE", so I'm wondering if I did well to include it.

71. Matt L. says:

=IF([@Citizen]="No",IF(AND((TODAY()-[@DOB])/365>=24,[@[Resident Since]]=21),"Yes",IF(AND([@Gender]="Female",(TODAY()-[@DOB])/365>=18),"Yes","No")))

• Matt L. says:

Wow! That is definitely not what I pasted into my comment!

• Matt L. says:

Here is what I meant to post, with GTE and LTE instead of the symbols.

=IF([@Citizen]="No",IF(AND((TODAY()-[@DOB])/365 GTE 24,[@[Resident Since]] LTE DATE(2006,1,1)),"Yes","No"),IF(AND([@Gender]="Male",(TODAY()-[@DOB])/365 GTE 21),"Yes",IF(AND([@Gender]="Female",(TODAY()-[@DOB])/365 GTE 18),"Yes","No")))

72. Emerson says:

=IF(OR(AND(C4="Female";E4="Yes";YEAR(TODAY())-YEAR(D4)>=18);AND(C4="Male";E4="Yes";YEAR(TODAY())-YEAR(D4)>=21);AND(E4="No";YEAR(TODAY())-YEAR(D4)>=24;TEXT(F4;"dd/mm/aaaa")>="01/01/2006"));"Can Vote";"Cannot")

73. Sam Mathai Chacko says:

=IF(E4="No",AND(E4&F4 LTE "No38718",(TODAY()-D4)/365.25 GTE 24),(TODAY()-D4)/365.25 GTE LOOKUP(E4&C4,{"N","YesF","YesM"},{24,18,21}))

74. Sam Mathai Chacko says:

A little modification

=(NOW()-C4)/365 GTE IF(D4="No",IF(E4 LE 38719,24),IF(B4="Male",21,18))

75. Sam Mathai Chacko says:

By the way, credit to that last one should go to my friend and colleague Debraj. I just took out a few characters from his formula. 😀

76. Sam Mathai Chacko says:

Here's my final version with 60 characters, but not with IF

=(NOW()-C4)/365 GTE LOOKUP(D4&B4,{"N","YesF","YesM"},{24,18,21})

77. Sam Mathai Chacko says:

On further analysis, realized that my last formula is flawed. But good challenge Chandoo

78. Boris says:

=IF(E3="No",IF(AND((TODAY()-D3)>8760,F3>1/1/2006),"Yes","No"),IF(C3="Male",IF((TODAY()-D3)>7665,"Yes","No"),IF((TODAY()-D3)>6570,"Yes","No")))

C3 = Male/Female Column
D3 = DOB
E3 = Citizen
F3 = Resident Date

79. Bill says:

No Ifs used

=CHOOSE(1+(([@Citizen]="yes")*([@Gender]="Male")*(TODAY()-[@[Date of Birth]] GT 21*365))+(([@Citizen]="yes")*([@Gender]="Female")*(TODAY()-[@[Date of Birth]] GT 18*365))+(([@Citizen]="No")*(TODAY()-[@[Date of Birth]] GT 24*365)*([@[Resident Since]] LTE DATEVALUE("01/01/2006"))),"No","Yes")

80. aylyn says:

=IF(E4="no",IF(YEARFRAC(D4,TODAY())>=24,"yes","no"),IF(C4="male",IF(YEARFRAC(D4,TODAY())>=21,"yes","no"),IF(C4="female",IF(YEARFRAC(D4,TODAY())>=18,"yes","no"))))

• aylyn says:

C4 = Gender
D4 = Date
E4 = Citizen

81. aylyn says:

Revised:

=IF(E4="no",IF(AND(YEARFRAC(D4,TODAY())>=24,(F4=21,"yes","no"),IF(C4="female",IF(YEARFRAC(D4,TODAY())>=18,"yes","no"))))

C4 = Gender
D4 = Date of Birth
E4 = Citizen
F4 = Resident Since

82. Rakesh says:

hi
What is the formula to add "/" symbol between date month and year automatically?

e.g 04072016 (4th July 2016) automatically converted to 04/07/2016 ??

pls help

• Hui... says:

@Rakesh

If the cell contains a true date you can use a Custom Number Format such as dd/mm/yyyy

If the cell contains text, which i suspect it does
Use a helper column and put =DATE(RIGHT(A2,4),MID(A2,3,2),LEFT(A2,2))
Then apply a Custom Number Format such as dd/mm/yyyy
Then copy/paste as values

or you can use Text To Columns
Select the text
Goto the data Tab, select Text to Columns
Select Delimitered
Next
Next
Select date, then select DMY
apply

• Michael (Micky) Avidan says:

@Rakesh,
If all you need is for displaying the dates but NOT for calculations and you want to see: 04/07/2016 upon hitting 'Enter' after typing: 04072016 – try to 'Custom Format' those cells with the following pattern:
00\/00\/0000
*** The symbols, between the 'Zeros', are not the character V but one 'Back slash' & one 'Forward slash'.

83. Rakesh says:

Thanks a ton Hui..

really I was looking for this type of formula for the last 5-6 Months.
during this I got some related formulas but problem was not solved according to my requirements (dd/mm/yyyy) format.
But with this one I am fully satisfied and my long-standing problem is resolved now.
Thanks a lot again dear Hui..
I would also like to thanks candoo.org

Thanks.

84. Rakesh says:

sorry

its chandoo.org not candoo.org

sorry

85. Rakesh says:

Thank you very much dear Micky for your helpful tips. This is wonderful
when it is only for displaying and not for calculating.

Thanks again

• Michael (Micky) Avidan says:

@Rakesh,
You are more than welcome.
If you need to perform calculations use a helper-column and type: =TEXT(A1,"00\/00\/0000") (Same pattern as above)

86. Rushabh Gala says:

=IF(OR(AND([@Gender]="Male",ROUNDDOWN((TODAY()-[@[Date of Birth]])/365,0)>=21,[@Citizen]="Yes"),AND([@Gender]="Female",ROUNDDOWN((TODAY()-[@[Date of Birth]])/365,0)>=18,[@Citizen]="Yes"),AND([@Citizen]="No",ROUNDDOWN((TODAY()-[@[Date of Birth]])/365,0)>=24,[@[Resident Since]]>38718))=TRUE,"Can Vote","Can not Vote")

I don't know if this formula is perfect, but it gave me satisfactory answer.

87. Rushabh Gala says:

=IF(OR(AND([@Gender]="Male",ROUNDDOWN((TODAY()-[@[Date of Birth]])/365,0)>=21,[@Citizen]="Yes"),AND([@Gender]="Female",ROUNDDOWN((TODAY()-[@[Date of Birth]])/365,0)>=18,[@Citizen]="Yes"),AND([@Citizen]="No",ROUNDDOWN((TODAY()-[@[Date of Birth]])/365,0)>=24,[@[Resident Since]]<38718))=TRUE,"Can Vote","Can not Vote")

the earlier formula had a minor mistake, kindly comment if this formula is correct.

88. Avnish Tiwari says:

Hello friends
I am a bit new on this forum still I have used my very tiny brain to solve this challenge for The great Chandoo, think it works fine for the problem
=IF(OR(AND(C2="MALE",DATEDIF(D2,TODAY(),"Y")GTE21,E2="YES"),AND(C2="FEMALE",DATEDIF(D2,TODAY(),"Y")GTE18,E2="YES"))),"YES",IF(AND(E2="NO",DATEDIF(D2,TODAY(),"Y")GTE24,DATEDIF(F2,TODAY(),"Y")GTE10,)"YES","NO"))

89. MVF says:

=IF(OR(AND([@Citizen]="yes",[@Gender]="Male",(TODAY()-[@[Date of Birth]])>=7665),AND([@Citizen]="yes",[@Gender]="Female",(TODAY()-[@[Date of Birth]])>=6570),AND([@Citizen]="No",(TODAY()-[@[Date of Birth]])>=8760,(TODAY()-IF([@[Resident Since]]="",0,[@[Resident Since]]))>=4089)),"Yes","No")

90. Chanchal Sahu says:

=IF(OR(AND(voters[@Citizen]="Yes",voters[@Gender]="Male",DATEDIF(voters[@[Date of Birth]],TODAY(),"Y")>=21),AND(voters[@Citizen]="Yes",voters[@Gender]="Female",DATEDIF(voters[@[Date of Birth]],TODAY(),"Y")>=18),AND(voters[@Citizen]="No",DATEDIF(voters[@[Date of Birth]],TODAY(),"Y")>=24,voters[@[Resident Since]]>DATE(2006,1,1))),"Can Vote","Can't Vote")

91. cutegal says:

=IF(AND([@Gender]="Male",YEAR(TODAY())-YEAR([@[Date of Birth]]) GTE 21,[@Citizen]="Yes"),"Yes",IF(AND([@Gender]="Female",YEAR(TODAY())-YEAR([@[Date of Birth]]) GTE 18,[@Citizen]="Yes"),"Yes",IF(AND(YEAR(TODAY())-YEAR([@[Date of Birth]]) GTE 24,[@Citizen]="No",[@[Resident Since]]<=DATE(2006,1,1)),"Yes","No")))

92. cutegal says:

=IF(AND([@Gender]="Male",YEAR(TODAY())-YEAR([@[Date of Birth]])>=21,[@Citizen]="Yes"),"Yes",IF(AND([@Gender]="Female",YEAR(TODAY())-YEAR([@[Date of Birth]])>=18,[@Citizen]="Yes"),"Yes",IF(AND(YEAR(TODAY())-YEAR([@[Date of Birth]])>=24,[@Citizen]="No",[@[Resident Since]]<=DATE(2006,1,1)),"Yes","No")))

 « Excel Tips, Tricks, Cheats & Hacks – Microsoft MVP Edition Earth Venus cosmic dance – Animated chart in Excel »

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.