Formula fields allow you to create new fields that do calculations on other available fields in the same view. These new fields can be displayed next to the other fields in a list view or on an Edit window (Edit Account, Edit Event, and so on). They are read-only fields. Formula Field calculations are based on Excel formulas; they use identical language and logic.
For more information on how to create formula fields, see Custom Fields.
The more commonly used formula fields and a few tips and tricks on less obvious ways to use them are:
- Convert date to only month for trend reporting:
=TEXT(field,”mmmm”) for full month or
=TEXT(field,”mmm”) for abbreviated month
- Convert date to only year for trend reporting: =YEAR(field)
- Attendees per day:
- Subject: Events (1)
- Formula: ={Ordered Attendance}/{Event Days}
- Data Type: Numeric
- Format: No Decimals
- Birthday card list for contacts:
- Month:
- Subject: All Individual Accounts (286)
- Formula: ={Birth Date}
- Data Type: Date Time
- Format: Month
- Day
- Subject: All Individual Accounts (286)
- Formula: =Day({Birth Date})
- Data Type: Numeric
- Format: No Decimals
- Month:
- Summarize by Year Amounts: When the fiscal year is different than the calendar year and you want to summarize by year amounts using event start date. In the formula below, +4 adds 4 months to the year end:
=TEXT(DATE(YEAR({Start Month-Year}),MONTH({Start Month-Year})+4,1),"yyyy")
- List by Email Prefix: Pulls everything prior to the @ for email addresses:
=IF(ISERR(FIND("@",{Email},1)), "", MID({Email},1,FIND("@",{Email},1)-1)) - List 30 days prior to an event: =TEXT({30 Days Prior},"mmmm d, yyyy")
- Balance Due after Security Deposit: =sum({Service Order Items Total Due}-{Security Deposit})
- End date written out with long month: =TEXT({End Date},"mmmm d, yyyy")
- Booking release date: ={Booking Date}-14
- Net revenue: ={Ordered Total}-{Ordered Cost Total}
- Calculate a certain number of days before/after event start:
=TEXT(DATE(YEAR({Start Date}),MONTH({Start Date}),DAY({Start Date})-30),"mm/dd/yy")
Change the {Start Date} value to change what the date the formula calculation is based off of (Move In Date, etc.). Change the -30 at the end modifies the number of days out to calculate.
- Display dates in ordinal format (1st, 5th, etc.). Example below uses the Contract Written Date as the base date.
=DAY({Written Date})&IF(AND(MOD(DAY({Written Date}),100)>10,MOD(DAY({Written Date}),100)<14),"th",CHOOSE(MOD(DAY({Written Date}),10)+1,"th","st","nd","rd","th","th","th","th","th","th"))
The following formula fields require the use of Summary Fields which are only available with the Momentus Dashboard module.
- Per Capita (Caps)
- Create the summary field for revenue from the service order items.
- Only include ordered or actual numbers and exclude cancelled.
={Revenue}/{Attendance}
Comments
11 comments
How does the Date & Time option in a Formula work? I have many Date/Time formulas to create and when I choose that option with the "TEXT" formula it doesn't populate. I have to use the TEXT option instead of Date/Time options. This eats away at our 50 Text limit when we should be able to use the Date UDF limits.
1 upvotes
Hi Nina Dennison,
Can you tell us a little bit more about the field you have and what you need to do with it? What is the formula you have tried that does not work?
0 upvotes
Hello Sean Tame, Thanks for responding quickly. Below is an example of the formula I've been using. This formula does NOT populate on the Event when the Data Type is set to Date Time and Format is set to Date. The only way this formula populates on the Event is when I use the Data Type as Text. How do I change the formula to populate in the Event using a Date and Time option?
0 upvotes
Also, do you know how to create a formula for a date to populate with the next business day, instead of a weekend. Example, we want 30 prior to an event as a Start date for something, but we want to make sure it populates on Monday not Sunday. Thanks!
0 upvotes
When you expressly set the result to Text in the formula by using "=TEXT(" then the Data Type must also be set to Text. If you use ={In Date}-1 you will get the short date version of the Day before Move In.
The Format options unfortunately do not allow you to change the display of short date result. If you want it in long format, the only option is to convert it to Text, in which case you now have a text result.
Your formula can be simplified a little though, no need to convert the In Date to Date. Just use: =TEXT({In Date}-1,"mmmm dd,yyyy") and Data Type = Text.
Regarding the next business day. There is an Excel function called WORKDAY but unfortunately this is not supported. You can refer to this article (I presume it is up to date). Supported Excel Functions in Formula Fields – Ungerboeck Support Center
0 upvotes
Thank you very much Sean, you've helped greatly. It's a surprise that the WORKDAY function is not supported when this is a business that have so many companies with open hours of "9 to 5" Monday thru Friday. We may have events that start on weekends, but deliveries and office support are majority of workdays only. We need to setup a populated start date for our Receiving shipments, do you know how to create a UDF that allows us to input a Date and Time, without creating a dropdown option?
0 upvotes
My pleasure Nina.
I believe the functionality to create Excel based formula fields would be a third-party plug in and it would be there that the WORKDAY restriction comes in.
User defined fields can be Text, Numeric or Date based. Date based fields do not have a dropdown. You would need to create a UDF field with Type of Date (which presents a date picker) as well as a UDF with type Time (which gives a time entry)
Refer here for more info.
Configure Custom Fields/User Defined Fields (UDFs) – Ungerboeck Support Center
0 upvotes
Hi Sean,
I'm still not seeing the Date or Time picker options in the UDF's. The instructions talk about Validation Tables and questions. I've been searching for the Picker option, do you know who I can ask?
Thanks.
0 upvotes
Hi
What would be best way to group based on amount, the formaula below shows the result as True/False
When grouped together I get the following.
I would rather the True and False give me the grouping name, does anyone have a better way to group on a view based on amounts?
Thanks
Kris
0 upvotes
Kris Corrigan your current formula is simply asking 'is the ordered amount less than 500?'. You need to introduce an IF statement and output text.
Something like =IF({Ordered Amount}<500,"Orders less than 500","Orders 500 or greater") You could nest some more IFs to get other ranges.
0 upvotes
Thanks Sean, a nested IF worked a treat
0 upvotes
Please sign in to leave a comment.