Start a new topic

Format Time fields

I have a data file I am trying to import in which the start and end dates are in the following format:
2012/01/03 15:04:00.000

(so this is 3:04 pm military time)

I was wondering if anyone could provide the necessary regular expression that I could put in a dictionary to strip off the date and convert time to 3:04 pm?

Hi Berry,

Your post prompted me to do some testing in the front end of RE, and I found a bit of behavior that might impact how you choose to import your Action Start and End times.

If you enter times NOT in military (24HR Clock) time, then RE automatically assigns times during the hours of 8-11 to be AM, and times during the hours of 12-7 to be PM. This could cause early or late actions (calls, and breakfast or dinner meetings, came immediately to mind) to have the incorrect times.

It could also cause the actions to be unable to be saved. Example: If you enter a Start time of 700 then RE sets PM, and if you enter the End time of 800 then RE sets AM. When you try to save the action, RE will give an error that the Action cannot be saved because the start time cannot be after the end time. The Action would return the same exception during an import.

If you use military time, RE seems to handle all of the hours correctly. We also tested IOM to be sure it would properly handle military time (we used your file from your Support case for this import), and IOM handled the military time without issue.

This leads me to suggest that you may wish to leave your times in the 24HR format to be sure RE sets the correct times on your actions, and to prevent exceptions that RE's default behavior could cause.

Hope this helps!



Tiffany - I was not aware of this issue with times. Are there any dictionary, REg EX or API solutions for this if you did use non-military time to be sure that an action from 7:00 - 8:00 does not cause an exception?

Hi Berry and Melissa,

I realized my original reply was not quite complete:

Using military time only fixes the quirk for afternoon hours (1300-2359). In my testing, RE interprets 0700 as 7:00PM, not as 7:00AM, so you would still need to import in a time value that includes AM or PM to be sure the time gets set to the hour you intend. IOM and RE both handle importing a value of "7:00 AM" without an issue, in my testing.

When I tested further in the front end of RE, I could find no way to toggle/keystroke between AM and PM in the Action times fields when you enter them manually. You have to tab out, let RE pick AM or PM, and then click back in the field and change the AM/PM value as needed.

I think it would be possible to devise a RegEx formula to strip off everything before the space and after the period in Berry's data example, but you would still be left with the issue of how RE will interpret the time value (07:30:00 will be interpreted as 7:30PM). Given this extra requirement, it may be easier to write an IOM API function to process the value.

If you wanted to try the RegEx route, you might post your question on a site with some hardcore RegEx'ers, like

Hope this added bit of information helps!



Thanks for all the info and testing Tiffany, I'll see what I come up with!
Login or Signup to post a comment