Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to calculate age before 1/1/1900 in Excel?

In Excel, the date before 1/1/1900 cannot be entered and calculated correctly. If you want to calculate the age of a person who was born and died before 1/1/1900, that would not get the correct calculation in Excel generally. But here in this article, a trick can help you to calculate the age before 1/1/1900 in Excel.

Calculate age before 1/1/1900 with VBA

Calculate age by birthday with Kutools for Excel good idea3


Calculate age based on birthday

Here you have a list of birthday for instance, and have you ever tried to calculate the age based on thses birthday? Kutools for Excel's Calculate age based on birthday can do you a nice favor.
doc calculate age

arrow blue right bubble Calculate age before 1/1/1900 with VBA


Here is a VBA code can help you to quickly calculate the age before 1/1/1900.

Kutools for Excel, with more than 120 handy Excel functions, enhance your working efficiency and save your working time.

1. Select a blank cell and enter the birthday of the person, and then select another cell to enter the date of today, or the date of person die as you need. See screenshot:
doc calculate age before 1900 1

2. Then press Alt + F11 keys to enable Microsoft Visual Basic for Applications window.

3. Click Insert > Module, paste below VBA to the new Module window.

VBA: Calculate age before 1/1/1900

Public Function AgeFunc(SDate As Variant, EDate As Variant) As Long
'UpdatebyExtendOffice20160429
    Dim xSMonth As Integer
    Dim xSDay As Integer
    Dim xSYear As Integer
    Dim xEMonth As Integer
    Dim xEDay As Integer
    Dim xEYear As Integer
    Dim xAge As Integer
    If Not GetDate(SDate, xSYear, xSMonth, xSDay) Then
        AgeFunc = "Invalid Date"
        Exit Function
    End If
    If Not GetDate(EDate, xEYear, xEMonth, xEDay) Then
        AgeFunc = "Invalid Date"
        Exit Function
    End If
    xAge = xEYear - xSYear
    If xSMonth > xEMonth Then
        xAge = xAge - 1
    ElseIf xSMonth = xEMonth Then
        If xSDay > xEDay Then xAge = xAge - 1
    End If
    If xAge < 0 Then
        AgeFunc = "Invalid Date"
    Else
        AgeFunc = xAge
    End If
End Function

Private Function GetDate(ByVal DateStr As String, Y As Integer, M As Integer, D As Integer) As Boolean
    Dim I As Long
    Dim K As Long
    Y = 0
    M = 0
    D = 0
    GetDate = True
    On Error Resume Next
    I = InStr(1, DateStr, "/")
    M = CLng(Left(DateStr, I - 1))
    D = CLng(Mid(DateStr, I + 1, InStr(I + 1, DateStr, "/") - I - 1))
    Y = CLng(Right(DateStr, Len(DateStr) - InStrRev(DateStr, "/")))
    If M < 1 Or M > 12 Or D < 1 Or D > 31 Or Y < 1 Then
        GetDate = False
    End If
End Function

4. Select a blank cell, here is C2, and enter this formula =AgeFunc(A2,B2) (A2 is the birthday, and the B2 is the end date), and press Enter key to get the age, and drag autofill handle to over the cells you need. See screenshot:
doc calculate age before 1900 2

Note: This VBA can only calculate ages with dates in the format of mm/dd/yyyy, such as 6/23/1845.


arrow blue right bubble Calculate age by birthday with Kutools for Excel

If the birthday dates are normally after 1/1/1900, you can apply Kutools for Excel’s Calculate age based on birthday utility, which can quickly calculate the age by the given birthdays.

Kutools for Excel, with more than 120 handy Excel functions, enhance your working efficiency and save your working time.

After free installing Kutools for Excel, please do as below:

1. Select a blank cell you want to output the age, and click Kutools > Formulas > Calculate age based on birthday. See screenshot:
doc calculate age before 1900 3

2. Then in the Formula Helper dialog, select the birthday you want to calculate age based on to the Date textbox. See screenshot:
doc calculate age before 1900 4

3. Click Ok to apply this formula, and now you can see the age has been shown. And if you need, you can drag the autofill handle to over the cells needed this formula. See screenshot:
doc calculate age before 1900 5

pay attention1If you are interested in this addi-in, download the 60-days free trial.


Recommended Productivity Tools for Excel

Kutools for Excel Helps You Always Finish Work Ahead of Time, and Stand Out From Crowd

  • More than 300 powerful advanced features, designed for 1500 work scenarios, increasing productivity by 70%, give you more time to take care of family and enjoy life.
  • No longer need memorizing formulas and VBA codes, give your brain a rest from now on.
  • Become an Excel expert in 3 minutes, Complicated and repeated operations can be done in seconds, 
  • Reduce thousands of keyboard & mouse operations every day, say goodbye to occupational diseases now.
  • 110,000 highly effective people and 300+ world-renowned companies' choice.
  • 60-day full features free trial. 60-day money back guarantees. 2 years of free upgrade and support.

Brings Tabbed Browsing and Editing to Microsoft Office, Far More Powerful Than The Browser's Tabs

  • Office Tab is designed for Word, Excel, PowerPoint and Other Office Applications: Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.