Forum Discussion

ITSupport's avatar
ITSupport
Qrew Trainee
6 years ago

Date Time Format of MM/DD/YYYY HH:MM for integration to GoCanvas

Been going round and round to maintain this format.  I cannot pass the AM or PM of the time and would like it to be in 24 hour format but that doesn't hold either.  So created formula to take date field and Time of Day field to create the format I need.  This works fine except I either lose the leading zero for single digit hours or lose the format if format time ie12:00.  This is my formula:   ToText([Date Scheduled])&" "&Left("0"&ToText([Appointment Time]),5)  This works fine when it is 1:00 because of the added Zero in formula but when I go to 10:00, 11:00, 12:00 its get out of whack.  05-12-2018 01:00 - perfect with above formula but if time change to 10:00 I get this 05-12-2018 010:0    Basically I need the leading Zero for 1-9 hours but not when 10-12 hours in time of day.  Any help is appreciated! 
Formulas and functions

7 Replies

Sort By
  • _anomDiebolt_'s avatar
    _anomDiebolt_
    Qrew Elite
    6 years ago
    I think you should be using Right() instead of Left():
    ToText([Date Scheduled]) &
    " " & 
    Right("0" & ToText([Appointment Time]), 5) 
  • ITSupport's avatar
    ITSupport
    Qrew Trainee
    6 years ago
    Tried that but problem with "Right" is then I get AM and PM and can't have that. 
    • _anomDiebolt_'s avatar
      _anomDiebolt_
      Qrew Elite
      6 years ago
      I tested this and it works:

      Right("0" & NotRight(ToText([Appointment Time]), 3), 5)

      Using short field names because I am lazy:

      • YobaniMaldonado's avatar
        YobaniMaldonado
        Qrew Member
        2 years ago
        Nice work! 

        Is there a way to include seconds as well. I had the same issue trying to get rid of the PM and AM and this worked, but I need seconds to be recorded as well. Is that possible? 

        Thank you.

        ------------------------------
        Yobani Maldonado
        ------------------------------
    • _anomDiebolt_'s avatar
      _anomDiebolt_
      Qrew Elite
      6 years ago
      To trim off the " AM" or " PM" from the right try this:
      ToText([Date Scheduled]) &
      " " &
      NotRight(Right("0" & ToText([Appointment Time]), 5), 3)
    • ITSupport's avatar
      ITSupport
      Qrew Trainee
      6 years ago
      Thank you - this is super close.   Made a minor tweak to get it to display right  - used 8 instead of 5.   But still having issue with the leading zero

      With below formula:

      08:00  displays correctly  08:00
      10:00 displays this 010:00

      Some how need to add zero when time is <10:00 but not display for 10:00-12:00 if that makes sense

      ToText([Date Scheduled]) &" " &
      NotRight("0" & Right(ToText([Appointment Time]), 8), 3)