Forum Discussion

MattMatt1's avatar
MattMatt1
Qrew Trainee
8 years ago

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

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!
  • DavidHawe's avatar
    DavidHawe
    Qrew Trainee
    8 years ago
    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
  • MattMatt1's avatar
    MattMatt1
    Qrew Trainee
    8 years ago
    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!
    • DavidHawe's avatar
      DavidHawe
      Qrew Trainee
      8 years ago
      Have you tried merely using ToText([Contract Signing Time])
    • DavidHawe's avatar
      DavidHawe
      Qrew Trainee
      8 years ago
      You can use right and left statement based on the : to trim the colon and the am/pm out
  • LisaSell1's avatar
    LisaSell1
    Qrew Trainee
    8 years ago
    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)))