[Excel] Age calculator

Discussion in 'Mixed Languages' started by Zenkaino lovelive, Jul 13, 2022.

Tags:
  1. Zenkaino lovelive

    Zenkaino lovelive MDL Member

    Nov 18, 2019
    206
    49
    10
    #1 Zenkaino lovelive, Jul 13, 2022
    Birth Date: A2
    Base Date: B2

    Age in years, months and days (1st day of the life = 1 day old)

    Code:
    =IF(OR(MONTH(B2)>MONTH(A2),AND(MONTH(B2)=MONTH(A2),DAY(B2)>=DAY(A2))),YEAR(B2)-YEAR(A2),
IF(AND(MONTH(A2)=2,DAY(A2)=29,MONTH(B2)=2,DAY(B2)=28,DAY(EOMONTH(B2,0))=28),YEAR(B2)-YEAR(A2),YEAR(B2)-YEAR(A2)-1))&" years"

=IF(AND(MONTH(B2)>=MONTH(A2),DAY(B2)>=DAY(A2)),MONTH(B2)-MONTH(A2),
IF(AND(MONTH(B2)<MONTH(A2),DAY(B2)>=DAY(A2)),12+MONTH(B2)-MONTH(A2),
IF(AND(MONTH(B2)>MONTH(A2),DAY(B2)<DAY(A2)),
IF(AND(OR(DAY(A2)=29,DAY(A2)=30,DAY(A2)=31),MONTH(B2)=2,DAY(B2)=28,DAY(EOMONTH(B2,0))=28),MONTH(B2)-MONTH(A2),
IF(AND(OR(DAY(A2)=30,DAY(A2)=31),MONTH(B2)=2,DAY(B2)=29,DAY(EOMONTH(B2,0))=29),MONTH(B2)-MONTH(A2),
IF(AND(DAY(A2)=31,OR(MONTH(B2)=4,MONTH(B2)=6,MONTH(B2)=9,MONTH(B2)=11),DAY(B2)=30),MONTH(B2)-MONTH(A2),MONTH(B2)-MONTH(A2)-1))),
IF(AND(MONTH(A2)=2,DAY(A2)=29,MONTH(B2)=2,DAY(B2)=28,DAY(EOMONTH(B2,0))=28),0,
IF(AND(OR(DAY(A2)=29,DAY(A2)=30,DAY(A2)=31),MONTH(B2)=2,DAY(B2)=28,DAY(EOMONTH(B2,0))=28),12+MONTH(B2)-MONTH(A2),
IF(AND(OR(DAY(A2)=30,DAY(A2)=31),MONTH(B2)=2,DAY(B2)=29,DAY(EOMONTH(B2,0))=29),12+MONTH(B2)-MONTH(A2),
IF(AND(DAY(A2)=31,OR(MONTH(B2)=4,MONTH(B2)=6,MONTH(B2)=9,MONTH(B2)=11),DAY(B2)=30),12+MONTH(B2)-MONTH(A2),11+MONTH(B2)-MONTH(A2))))))))&" months"

=IF(OR(C2<>"0 years",D2<>"0 years"),
IF(DAY(B2)>=DAY(A2),DAY(B2)-DAY(A2),
IF(AND(DAY(B2)<DAY(A2),DAY(A2)<=28),
IF(MONTH(B2)-1=2,DAY(EOMONTH(B2,-1))+DAY(B2)-DAY(A2),
IF(OR(MONTH(B2)-1=4,MONTH(B2)-1=6,MONTH(B2)-1=9,MONTH(B2)-1=11),30+DAY(B2)-DAY(A2),DAY(EOMONTH(A2,-1))+DAY(B2)-DAY(A2))),
IF(AND(DAY(B2)<DAY(A2),DAY(A2)=29),
IF(MONTH(B2)-1=2,29+DAY(B2)-DAY(A2),
IF(OR(MONTH(B2)-1=4,MONTH(B2)-1=6,MONTH(B2)-1=9,MONTH(B2)-1=11),30+DAY(B2)-DAY(A2),
IF(AND(MONTH(A2)=2,DAY(A2)=29,MONTH(B2)=2,DAY(B2)=28,DAY(EOMONTH(B2,0))=28),0,
IF(AND(DAY(A2)=29,MONTH(B2)=2,DAY(B2)=28,DAY(EOMONTH(B2,0))=28),0,DAY(EOMONTH(A2,-1))+DAY(B2)-DAY(A2))))),
IF(AND(DAY(B2)<DAY(A2),DAY(A2)=30),
IF(OR(MONTH(B2)-1=2,MONTH(B2)-1=4,MONTH(B2)-1=6,MONTH(B2)-1=9,MONTH(B2)-1=11),30+DAY(B2)-DAY(A2),
IF(AND(DAY(A2)=30,MONTH(B2)=2,DAY(B2)=28,DAY(EOMONTH(B2,0))=28),0,
IF(AND(DAY(A2)=30,MONTH(B2)=2,DAY(B2)=29,DAY(EOMONTH(B2,0))=29),0,DAY(EOMONTH(A2,-1))+DAY(B2)-DAY(A2)))),
IF(AND(DAY(A2)=31,MONTH(B2)=2,DAY(B2)=28,DAY(EOMONTH(B2,0))=28),0,
IF(AND(DAY(A2)=31,MONTH(B2)=2,DAY(B2)=29,DAY(EOMONTH(B2,0))=29),0,
IF(AND(DAY(A2)=31,OR(MONTH(B2)=4,MONTH(B2)=6,MONTH(B2)=9,MONTH(B2)=11),DAY(B2)=30),0,31+DAY(B2)-DAY(A2)))))))),
IF(DAY(B2)>=DAY(A2),DAY(B2)-DAY(A2)+1,
IF(AND(DAY(B2)<DAY(A2),DAY(A2)<=28),
IF(MONTH(B2)-1=2,DAY(EOMONTH(B2,-1))+DAY(B2)-DAY(A2)+1,
IF(OR(MONTH(B2)-1=4,MONTH(B2)-1=6,MONTH(B2)-1=9,MONTH(B2)-1=11),30+DAY(B2)-DAY(A2)+1,DAY(EOMONTH(A2,-1))+DAY(B2)-DAY(A2)+1)),
IF(AND(DAY(B2)<DAY(A2),DAY(A2)=29),
IF(MONTH(B2)-1=2,29+DAY(B2)-DAY(A2)+1,
IF(OR(MONTH(B2)-1=4,MONTH(B2)-1=6,MONTH(B2)-1=9,MONTH(B2)-1=11),30+DAY(B2)-DAY(A2)+1,
IF(AND(MONTH(A2)=2,DAY(A2)=29,MONTH(B2)=2,DAY(B2)=28,DAY(EOMONTH(B2,0))=28),1,
IF(AND(DAY(A2)=29,MONTH(B2)=2,DAY(B2)=28,DAY(EOMONTH(B2,0))=28),1,DAY(EOMONTH(A2,-1))+DAY(B2)-DAY(A2)+1)))),
IF(AND(DAY(B2)<DAY(A2),DAY(A2)=30),
IF(OR(MONTH(B2)-1=2,MONTH(B2)-1=4,MONTH(B2)-1=6,MONTH(B2)-1=9,MONTH(B2)-1=11),30+DAY(B2)-DAY(A2)+1,
IF(AND(DAY(A2)=30,MONTH(B2)=2,DAY(B2)=28,DAY(EOMONTH(B2,0))=28),1,
IF(AND(DAY(A2)=30,MONTH(B2)=2,DAY(B2)=29,DAY(EOMONTH(B2,0))=29),1,DAY(EOMONTH(A2,-1))+DAY(B2)-DAY(A2)+1))),
IF(AND(DAY(A2)=31,MONTH(B2)=2,DAY(B2)=28,DAY(EOMONTH(B2,0))=28),1,
IF(AND(DAY(A2)=31,MONTH(B2)=2,DAY(B2)=29,DAY(EOMONTH(B2,0))=29),1,
IF(AND(DAY(A2)=31,OR(MONTH(B2)=4,MONTH(B2)=6,MONTH(B2)=9,MONTH(B2)=11),DAY(B2)=30),1,31+DAY(B2)-DAY(A2)+1))))))))&" days"