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

  • 0
  • 1
  • Question
  • Updated 8 months ago
  • Answered
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!
Photo of Matt

Matt

  • 60 Points

Posted 8 months ago

  • 0
  • 1
Photo of dwhawe

dwhawe, Champion

  • 662 Points 500 badge 2x thumb
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"
Photo of Matt

Matt

  • 60 Points
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!
Photo of dwhawe

dwhawe, Champion

  • 622 Points 500 badge 2x thumb
Have you tried merely using ToText([Contract Signing Time])
Photo of dwhawe

dwhawe, Champion

  • 622 Points 500 badge 2x thumb
You can use right and left statement based on the : to trim the colon and the am/pm out
Photo of Lisa

Lisa

  • 210 Points 100 badge 2x thumb
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)))
Photo of Matt

Matt

  • 60 Points
Perfect! That worked! Thank you very much!