Calculated Field - Displaying the Month of a Date Field
Overview
This article will present a Sugar Logic formula that isolates the month portion of a date field into a custom calculated field. We then extend this field's use to create another custom calculated field that displays both the month and year of the date field.
For an overview on Sugar Logic and Calculated Fields, please refer to the article Introduction to Calculated Fields.
Use Case
In this example formula, we will extract the month portion of the Opportunities module's "Expected Close Date" to a custom field. The example close date is 10/01/2012.
Note: Beginning with version 9.1.0, each user's license type (e.g. Sugar Serve) determines what functionality is available as described in the License Types Matrix documentation. The Opportunities module used as an example in this article is not available for Sugar Serve users, but creating the custom calculated field will work similarly for other modules.
Prerequisites
You must be an administrator or have developer-level role access in Sugar to create custom fields and modify layouts. For more information, please refer to the Developer Tools documentation.
Note: This article is written for a Sugar instance that is configured to use Opportunities without Revenue Line Items. If your instance utilizes the Revenue Line Items module, simply perform the same steps below for the Revenue Line Items module instead of the Opportunities module.
Steps to Complete
Create a new field of type TextField in the Opportunities module and mark it as "Calculated". For our example, we named the field $month_closed_c
. with the display label "Month Closed".
To display the month based on the expected close date, copy the following formula into the "Edit Formula" window for $month_closed_c
:
monthofyear($date_closed)
If you would like to isolate both the month and the year of the date field, then you can create a second TextField field in the Opportunities module with the following formula that references the previously calculated field, $month_closed_c
. For this example, we named the new field $month_year_c
with the display label "Month and Year", which you can see in the layout pictured in the Application section.
concat($month_closed_c,"/",
ifElse(isInList(subStr(toString($date_closed),2,1),createList(".","-","/")),
subStr(toString($date_closed),6,4),
subStr(toString($date_closed),0,4)))
Formula Breakdown
The monthofyear()
function returns the month portion of the given date field. For our example, the monthofyear()
function returns 10 as the Expected Close Date field's value is 10/01/2012.
The second formula goes through some additional steps to include the year in its result. In Sugar, dates may have the year coming first or last depending on your date format. Also dependent on the date format is whether the date's day, month, and year are separated by a slash, period, or hyphen. To account for these differences, the second function uses an ifElse()
function that will check if the third character of the date is a period, dash, or slash. If so, it assumes that the year comes at the end of the date because the month or day is only two characters long while the year is four characters. The formula then uses the subStr()
function to grab four characters starting with the seventh character as the year. If the ifElse()
evaluates false, then we know that the year comes at the beginning of the date and thus the formula uses the second subStr()
to take the first four characters as the year. Both subStr()
functions also use the toString()
function to change the date-type field $date_closed
into a string because substring()
only works with string fields.
After using subStr()
to isolate just the third character, createList()
generates a temporary list of the potential separator characters so that the isInList()
function can determine if the third character of the date is a separator character. Whether this isInList()
match returns true or false determines whether the first or second subStr()
is returned from the ifElse()
.
Finally, the concat()
function joins the month, the character "/", and the year returned by the ifElse()
into a single string (e.g. "mo/year"). For our example date of 10/01/2012, this function returns the string "10/2012".
Application
Once you have created the custom text fields with their calculated value formulas, add the field(s) to the module's record view to display the month and/or month and year that the opportunity is expected to close.