---
title: Derived property formula examples
description: "Worked derived property formulas for customer age, days since registration, date differences, standardized date formats, value replacement, and array length."
product: data
lang: en
last_updated: 2026-11-20
token_estimate: 1748
---
# Derived property formula examples

These are complete, copyable formulas for common [derived property](https://amplitude.com/docs/data/derived-properties) tasks. For the full list of available functions and operators, refer to [Derived property functions and operators](https://amplitude.com/docs/data/derived-properties/functions).

> For AI agents: a documentation index is available at [/docs/llms.txt](/docs/llms.txt). Append `.md` to any page URL for markdown, or send `Accept: text/markdown`.

## Plan availability

Derived Properties is available on the Growth and Enterprise plans. Go to [Amplitude pricing](https://amplitude.com/pricing) for plan details.

| Plan | Allowance | How to access |
| --- | --- | --- |
| Free | Not included | [Upgrade your plan](https://amplitude.com/pricing) |
| Plus | Not included | [Upgrade your plan](https://amplitude.com/pricing) |
| Growth | Included | Included in plan |
| Enterprise | Included | Included in plan |

_Availability reflects the latest Amplitude plans. If your organization is on an older plan, your access may differ. Check your plan under [Plans & Billing in your organization settings](/docs/admin/account-management/account-settings#organizational-settings), or contact your Amplitude account representative._

## Calculate the age of a customer

```plaintext
CEIL(
    DIVIDE(
        MINUS(
            PROPERTY('server_upload_time', 'amplitude_user'),
            TIME_TO_LONG(PROPERTY('Created At', 'user'))
        ),
        86400000
    )
)
```

Use this formula when you track a user property with a date-time data type and want to calculate the age of that user (how long that user has existed in your system) since the event that set this user property.

## Calculate days since registration

Use `Event Time` with a date or datetime property to calculate how many days passed between an event and a fixed date, such as a user's registration date. In the derived property builder, select `Event Time` from the _Insert Property_ dropdown, then build a formula like this:

```plaintext
DIVIDE(
    MINUS(
        PROPERTY('Event Time', 'event'),
        TIME_TO_LONG(PROPERTY('registration_date', 'user'))
    ),
    86400000
)
```

This example assumes `registration_date` is a datetime string (for example, `"2023-08-18 17:37:39"`). Inside a formula, `Event Time` acts as a long in epoch milliseconds. The formula converts the registration date to a long with `TIME_TO_LONG`, subtracts it from `Event Time`, and divides by 86400000, the number of milliseconds in one day.

## Get the difference between two dates

```plaintext
DIVIDE(  
  MINUS(  
    DATE_TO_LONG(  
        PROPERTY(  
            'start_date', 'user'  
        )  
    ),  
    DATE_TO_LONG(  
        PROPERTY(  
            'end_date', 'user'  
        )  
    )  
  ),  
  86400000   
)
```

Sample output.

In the derived property above, Amplitude converts the properties `end_date` and `start_date` into UNIX timestamps to calculate the difference between them. Amplitude then divides that result by 86400000, the number of milliseconds in one day.

> **Note:**
>
> This output is a double type (for example, 2.0).

## Output a standardized date format

```plaintext
IF(  
    DATE_TIME_FORMATTER(  
        PROPERTY(  
            'publishDate',  
            'event'  
        ),  
        "yyyy-MM-dd'T'HH:mm:ssX",  
        'yyyy-MM-dd'  
    ) 
    contains '-',  
    DATE_TIME_FORMATTER(  
        PROPERTY(  
            'publishDate',  
            'event'  
        ),  
        "yyyy-MM-dd'T'HH:mm:ssX",  
        'yyyy-MM-dd'  
    ),  
    IF(  
        DATE_TIME_FORMATTER(  
            PROPERTY(  
                'publishDate',  
                'event'  
            ),  
            "yyyy-MM-dd HH:mm:ss",  
            'yyyy-MM-dd'  
        ) 
        contains '-',  
        DATE_TIME_FORMATTER(  
            PROPERTY(  
                'publishDate',  
                'event'  
            ),  
            "yyyy-MM-dd HH:mm:ss",  
            'yyyy-MM-dd'  
        ),  
        DATE_TIME_FORMATTER(  
            PROPERTY(  
                'publishDate',  
                'event'  
            ),  
            "yyyy-MM-dd",  
            'yyyy-MM-dd'  
        )  
    )  
)
```

Sample output.

One way to format dates to Standard Date Format is to use a series of IF statements. Make sure the higher specificity conditional comes first. Replace the `$<number>` here with the actual properties.

## Get the month and year a customer signed up

```plaintext
CONCAT(  
    REGEXEXTRACT(  
        PROPERTY(  
            'start_date',  
            'user'  
        ),  
        'dddd-dd'  
    ), "-01"  
)
```

In this example, the derived property pulls the sign-up month and year from a property that contains a more detailed value, then appends "-01" to set it to the beginning of the month. Use `REGEXEXTRACT()` to pull the year and month from the value, and use `CONCAT()` to append "-01". Replace the `$<number>` here with the actual properties.

## Replace existing property values

```plaintext
IF(
    OR(
        REGEXEXTRACT(
            PROPERTY(
                "package",
                "event"
                ),
            'Casual'
        ) =='Casual',
        REGEXEXTRACT(
            PROPERTY(
                "package",
                "event"
            ),
            '1 Job Posting'
        )=='1 Job Posting',
        REGEXEXTRACT(
            PROPERTY(
                "package",
                "event"
            ),
            '1 Basic'
        )=='1 Basic'
    ),
    'Casual',
    'False'
)
```

Sample output.

To replace multiple property values, use `REGEXEXTRACT()` to pull the string from the property, and use OR statements inside an IF statement to check whether the value pulled from the properties contains any of the values you want to replace. In this example, if the property value matches any of the specified values, the formula replaces the value with `Casual`. Otherwise, the formula replaces the property with `False`. Replace the `$<number>` here with the actual properties.

## Count length of an array that was accidentally ingested as a string

Convert the string to an array format by using SPLIT:

```plaintext
SPLIT(  
PROPERTY('color', 'event'),  
','  
)
```

Sample output:

![Screenshot 2023-09-28 at 14.39.54.png](https://amplitude.com/docs/images/data/screenshot-2023-09-28-at-14-39-54-png.png)

Amplitude ingested the property color as the string "Red, Green, Blue". After using SPLIT, the resulting value is `[Red, Green, Blue]`.

Use the above derived property within a new derived property that uses `ITEM_COUNT`.

```plaintext
ITEM_COUNT(  
PROPERTY("Transform into Array", "derived")  
)
```

Sample output:

![Screenshot 2023-09-28 at 14.42.30.png](https://amplitude.com/docs/images/data/screenshot-2023-09-28-at-14-42-30-png.png)

Now the underlying data is an array. `ITEM_COUNT` counts the number of items that make up the array.

