MIN for the win! Date stamp when an account became a customer ๐Ÿ“†

The Problem:

I recently wanted to date stamp when an account became a customer. For my purposes this date was the ‘Close Date’ of the very first won opportunity.

My first instinct was to reach for Process Builder (I love it!), but I had a niggling feeling I was over-complicating things and there was a better way.

As always with Salesforce, best practice dictates the simplest solution is the one you should use. So I did a little digging around the Answers Community and was reminded of the frequently overlooked MIN function.


The Solution:

Navigate to Setup > Object Manager > {Accounts} > Fields & Relationships and create a roll-up summary field:

  • Field Label = Customer Start Date
  • Summarised Object = Opportunities
  • Roll-up Type = MIN
  • Field to Aggregate = Close Date

min1

I also added filter criteria to ensure we only roll-up won opportunities:

  • Field = Won
  • Operator = equals
  • Value = TRUE

min2

I then decided to take it one step further by adding another custom field; this time, a formula to display the number of years since the account became a customer:

  • Data Type = Formula
  • Field Label = Customer Duration
  • Formula Return Type = Number
  • Decimal Places = 1
  • Enter a description and help text
  • Enter the following formula but use โ€˜Insertโ€™ to ensure you use the correct API name:
IF(NOT(ISBLANK(Customer_Start_Date__c)),(TODAY() - Customer_Start_Date__c) / 365, 0)

min3

Helpful hints:

  • To avoid errors, I only want to calculate the number of years if we have a customer start date! So, I included an IFย  statement combined with NOT + ISBLANK to check the field wasn’t empty.
  • By dividing by 365 I create the number of years – without this I would display the number of days.

The Result:

An easy way to see how long an account has been a customer and when their first won opportunity was!

min4


Gotchas & hints:

*You don’t have to use the ‘Close Date’; use whatever field makes sense to your business. Perhaps you have a ‘Contract Start Date’ field that would be more appropriate!

*Using a date field works, as long as the data is accurate enough. The ‘Close Date’ is no use to you if your users can close an opportunity without entering the correct date. As an example, consider adding a validation rule that gets your users to enter a close date within 7 days of today or whatever metric suits your business needs.


Useful links:

Salesforce Help: Formula Operators and Functions

Salesforce Help: Roll-Up Summary Field

Trailhead: Implement Roll-Up Summary Fields


Resources:

Customer Duration Formula:

IF(NOT(ISBLANK(Customer_Start_Date__c)),(TODAY() - Customer_Start_Date__c) / 365, 0)

5 thoughts on “MIN for the win! Date stamp when an account became a customer ๐Ÿ“†

      1. Looking forward to it. Q: what’s the reason for placing a zero at the end of the formula? Is it saying: return any value from ‘whatever’ to zero’?

        Like

      2. Because we are using an IF statement we have to tell it if x is true then return this value, if x is false then return a different value. So in the example we are saying if there is a customer start date then calculate the number of years, otherwise display 0 e.g. 0 years.

        Liked by 1 person

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s