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

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

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

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)

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!

---

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)

---