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

  • 0
  • 1
  • Question
  • Updated 6 months ago
  • Answered
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! 
Photo of IT Support

IT Support

  • 190 Points 100 badge 2x thumb

Posted 6 months ago

  • 0
  • 1
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,522 Points 20k badge 2x thumb
I think you should be using Right() instead of Left():
ToText([Date Scheduled]) &
" " & 
Right("0" & ToText([Appointment Time]), 5) 
(Edited)
Photo of IT Support

IT Support

  • 190 Points 100 badge 2x thumb
Tried that but problem with "Right" is then I get AM and PM and can't have that. 
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,272 Points 20k badge 2x thumb
To trim off the " AM" or " PM" from the right try this:
ToText([Date Scheduled]) &
" " &
NotRight(Right("0" & ToText([Appointment Time]), 5), 3)
Photo of IT Support

IT Support

  • 190 Points 100 badge 2x thumb
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)
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,272 Points 20k badge 2x thumb
I tested this and it works:

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

Using short field names because I am lazy:

Photo of IT Support

IT Support

  • 190 Points 100 badge 2x thumb
ah ok - you flipped the 3, 5   Thanks again!