Commonly Used Formula Fields



  • Nina Dennison

    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. 

  • Sean Tame

    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?

  • Nina Dennison

    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?  

  • Nina Dennison

    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!

  • Sean Tame

    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


  • Nina Dennison

    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? 

  • Sean Tame

    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

  • Nina Dennison

    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?  



  • Kris Corrigan


    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?



  • Sean Tame

    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.

  • Kris Corrigan

    Thanks Sean, a nested IF worked a treat

Please sign in to leave a comment.