Calculate Age using PowerApps

On the surface, the calculation for a person’s age in PowerApps seems simple enough: use the DateDiff function to take the current date and the difference between their birthdate and return in years. However, in PowerApps, it’s not quite so straight-forward! For example, let’s say the current date is 6/5/2025, and someone’s birthdate is 7/4/1989. Using DateDiff:

DateDiff(Date(1989,7,4),Date(2025,6,5),TimeUnit.Years)

The function returned 36 and not 35 like we would expect! The person wouldn’t be 36 until it’s 7/4/2025. Turns out, the PowerApps DateDiff function behaves differently than it’s Excel counterpart. Unlike DateDif function in Excel which, by default, calculates for years completed between two dates, PowerApps just returns the difference between the years.

Calculate Age within a Canvas PowerApp

To more precisely calculate the age of someone accurately, use the following PowerFx:

RoundDown((Now() - date_DOB.SelectedDate) / 365.25,0)

Note that I am using a date picker control called “date_DOB” to populate the date.

We can’t just divide by 365 because it wouldn’t be quite precise enough. Some years between the two dates may contain leap years and thus have an extra day hence the additional .25. We want the precise age of one’s birthday where are you “exactly” x years old. Though, in reality as time soldiers on, your age can be thought of a long decimal value to represent the years hours minutes seconds, etc. This level of precision is more used in scientific research type purposes.

Drinking Age Validation Example

In the US, the legal drinking age is 21. I want to take’s a users birthdate, and validate if they are old enough drink. First, we need the person’s age, and then determine if the age is greater than or equal to 21.

Taking advantage of User Defined Functions and Named Formulas in PowerApps, we can write more modular code:

nfminimumDrinkAge = 21;
CalculateAge (DateOfBirth:Date): Number = RoundDown(
    (Now() - DateOfBirth) / 365.25,
    0
);
IsLegalDrinkingAge(PersonAge:Number):Boolean = PersonAge >= nfminimumDrinkAge;

Within the Calculate Button, I used the following PowerFx to call the function:

// Run Named Formula
UpdateContext({varPersonAge: CalculateAge(date_DOB.SelectedDate)});
// Update context variables seperately so varPersonAge is evaluated with the latest data
UpdateContext({varLegalToDrink: IsLegalDrinkingAge(varPersonAge)})

Then, I pass the date picker selected date into the function for Calculating the Age. Using it’s age number output, we pass that into IsLegalDrinkingAge function to return true or false. For the text label:

$"Your age is {varPersonAge}." & $"{If(
    varLegalToDrink,
    $" You are legal to drink. 🍺",
    $" You are not legal to drink. ❌"
)}"

At the time of this writing, User Defined Functions are a preview feature. You may need to enable it them under your app settings > preview.

Return Age in Years, Months, and Days

We can also return someone’s age expressed as years, months, and days. Again, this turned out to be more complicated that I thought given the functionality differences with the DateDiff functions in PowerApps vs Excel! Place this code inside a label (this could also be turned into a User Defined Function!):

// Returns text of one's age as Years, Months, and Days from now
With(
    {
        // Calculate Years
        AgeInYears: RoundDown(
            (Now() - date_DOB.SelectedDate) / 365.25,
            0
        ),
        DateOfLastBirthday: Date(
            Year(Now()) - 1,
            Month(date_DOB.SelectedDate),
            Day(date_DOB.SelectedDate)
        )
    },
    With(
        {
            // Months passed since last Birthday
            MonthsSinceLastBirthday: Mod(
                DateDiff(
                    DateOfLastBirthday,
                    Now(),
                    TimeUnit.Months
                ) - If(
                    Day(DateOfLastBirthday) > Day(Now()),
                    1,
                    0
                ),
                12
            ),
            MonthsCompletedSinceLastBirthday: DateDiff(
                DateOfLastBirthday,
                Now(),
                TimeUnit.Months
            ) - If(
                Day(DateOfLastBirthday) > Day(Now()),
                1,
                0
            )
        },
        With(
            {
                // Get the days passed since the last month completed
                DaysSinceLastMonthToToday: DateDiff(
                    EDate(
                        DateOfLastBirthday,
                        MonthsCompletedSinceLastBirthday
                    ),
                    Now(),
                    TimeUnit.Days
                )
            },
            With(
                {
                    TextYears: If(
                        AgeInYears = 1,
                        "year",
                        "years"
                    ),
                    TextMonths: If(
                        MonthsSinceLastBirthday = 1,
                        "month",
                        "months"
                    ),
                    TextDays: If(
                        DaysSinceLastMonthToToday = 1,
                        "day",
                        "days"
                    )
                },
                // Return result
                $"{AgeInYears} {TextYears} {MonthsSinceLastBirthday} {TextMonths} {DaysSinceLastMonthToToday} {TextDays}"
            )
        )
    )
)

Calculated Columns for Age

If your use-case is to dynamically keep one’s age up-to-date in a PowerApps data source, then I would take a different approach. For SharePoint, one option is using a calculated column while in Dataverse, we can use a formula column.

Calculated Age Column in SharePoint

Within your SharePoint List, first ensure you have a date column to hold your person’s date of birth. Next, create a new column and make the type calculated. Use the following formula. My reference column is called “DateOfBirth” and the data type returned is number.

The end result:

A word of caution: Calculated Columns are not Delegable in PowerApps so this won’t be ideal for filtering on large lists above the default 500 (max 2000) delegation limit. Further, when filtering on it, you’ll need to cast the column as value. Example: Filter(Employees,Value(CalculatedAge) = 23). Filter the employees list where the calculated age column value is equal to 23. If this is your scenario, can you also use a daily scheduled Power Automate to calculate age and write to a number column instead.

Calculated Age Column in Dataverse

Using the a formula column in a Dataverse table makes quick work of this. In this example, I am using the out of the box Contacts table. It already has a column called “Birthdate”. I’ll add a new formula column:

Unlike with the SharePoint calculated columns and delegation limitations, Dataverse doesn’t have this drawback.

Calculate Age using Power Automate

Using my prior example of an Employees List in SharePoint, we can also create a scheduled daily Power Automate to recalculate one’s age and update an Age column. This would be a good work-around for the calculated column delegation limitation I mentioned earlier, as you could write to a number column instead which supports delegation.

div(div(sub(ticks(utcNow('yyyy-MM-dd')),ticks(item()['DateOfBirth'])),864000000000),365.25)

Note that the item()?[‘DateOfBirth’] is referencing the name of my column “DateOfBirth” as I loop through the list.

As you can see, there’s many different ways to handle this common, seemingly simple process. 🙂