Discussions

Expand all | Collapse all

Cannot figure out syntax to display a time of day in a formula text field

  • 1.  Cannot figure out syntax to display a time of day in a formula text field

    Posted 03-07-2018 20:48
    So I have a time of day formula that I am trying to put into a formula text field in my form. Here is the formula that I have written.

    If([Lockbox Needed?]="Yes", "Contract signing scheduled for "&ToFormattedText([Contract Signing Time],"HHMM")&". Also make sure to install lockbox", ToFormattedText([Contract Signing Time],"HHMM"))

    I am getting an error that throws up that ToFormattedText is the wrong syntax but I cannot seem to find what the correct syntax is. Please help!


  • 2.  RE: Cannot figure out syntax to display a time of day in a formula text field

    Posted 03-07-2018 23:34
    I don't see where you can use Time of Day with ToFormattedText.  The HHMM format looks like it meant for "Duration" fields.

    You may wan to consider using a Date/Time field rather than Time of Day with this:

    ToFormattedText([Date Created],"MMDDYY")  returns "01-30-00 10:34 PM


  • 3.  RE: Cannot figure out syntax to display a time of day in a formula text field

    Posted 03-08-2018 13:57
    I thought of that but unfortunately, I use a non-native app that updates and creates records in other locations of my system and I already have everything written using a separate date field. To change it at this point has ripple effects that would take longer than I have to spend on the project to correct. I know that my syntax is off and that ToFormattedText is not correct but is there any syntax that will convert a time of day field to display in a formula text field as part of a formula? Thanks for your suggestion!


  • 4.  RE: Cannot figure out syntax to display a time of day in a formula text field

    Posted 03-08-2018 16:35
    Have you tried merely using ToText([Contract Signing Time])


  • 5.  RE: Cannot figure out syntax to display a time of day in a formula text field

    Posted 03-08-2018 16:37
    You can use right and left statement based on the : to trim the colon and the am/pm out


  • 6.  RE: Cannot figure out syntax to display a time of day in a formula text field

    Posted 03-08-2018 16:41
    I useda Time of Day type field, the times display as AM or PM, and this worked to only show the HHMM in your statement:

    If([Lockbox Needed?]="Yes", 
    "Contract signing scheduled for "& 
    Trim(Left(ToText([Time of day]),5)) &". Also make sure to install lockbox", 
    Trim(Left(ToText([Time of day]),5)))


  • 7.  RE: Cannot figure out syntax to display a time of day in a formula text field

    Posted 03-08-2018 18:30
    Perfect! That worked! Thank you very much!