Discussions

Expand all | Collapse all

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

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

    Posted 05-11-2018 22:27
    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! 


  • 2.  RE: Date Time Format of MM/DD/YYYY HH:MM for integration to GoCanvas

    Top
    Contributor
    Posted 05-11-2018 23:09
    I think you should be using Right() instead of Left():
    ToText([Date Scheduled]) &
    " " & 
    Right("0" & ToText([Appointment Time]), 5) 


  • 3.  RE: Date Time Format of MM/DD/YYYY HH:MM for integration to GoCanvas

    Posted 05-11-2018 23:17
    Tried that but problem with "Right" is then I get AM and PM and can't have that. 


  • 4.  RE: Date Time Format of MM/DD/YYYY HH:MM for integration to GoCanvas

    Top
    Contributor
    Posted 05-13-2018 16:33
    To trim off the " AM" or " PM" from the right try this:
    ToText([Date Scheduled]) &
    " " &
    NotRight(Right("0" & ToText([Appointment Time]), 5), 3)


  • 5.  RE: Date Time Format of MM/DD/YYYY HH:MM for integration to GoCanvas

    Posted 05-14-2018 15:24
    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)


  • 6.  RE: Date Time Format of MM/DD/YYYY HH:MM for integration to GoCanvas

    Top
    Contributor
    Posted 05-14-2018 15:51
    I tested this and it works:

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

    Using short field names because I am lazy:



  • 7.  RE: Date Time Format of MM/DD/YYYY HH:MM for integration to GoCanvas

    Posted 05-14-2018 16:05
    ah ok - you flipped the 3, 5   Thanks again!