Calculation question regarding time

[Sergeant Ron]

I have Time fields that are currently set up to allow users to input the time as 1200. We use 2400 time format. Currently the time field is defined to leave data format as entered. I would like to go back and change all previously entered times to read 12:00. I would then change the time format of the field. I know I could setup a script and a loop to change all of the existing records. I was just wondering how I should create a calculation in the "set field" script step to accomplish this. Any input would be greatly appreiciated. Thanks, Ron.

[LaRetta]

Hi Ron,

You are using a time field now? Then I think FM is ahead of you. Time input is handled via field format (whether to use 12-hour, 24-hour or leave as entered). However, FM keeps track behind the scenes and internally tracks AM/PM properly.

If you change your field format time, it will convert automatically for you. FM is very forgiving on time fields. In other words, if Users type 1600 in a time field (with 'leave format as entered') and you change it to 12-hour notation with AM/PM, it will display 4:00 PM (or whatever you indicate in field format). Or are you saying you were using a text field? Because otherwise there is no need to do anything other than change the format. Am I missing your point here possibly?

LaRetta

[Sergeant Ron]

Hi Loretta, The problem I was having was that the users have been entering the time without the When I format the time to include the : it reads for example 1200:00 instead of 12:00.

[LaRetta]

Hi Ron,

If you leave a time field (as you've indicated with 'leave data as entered' and a User types 1200, it displays 1200:00:00. So, to get field results of 1200, you had to have set the time format to specifically OMIT the ':'.?? Regardless, you now have a field with 1200:00:00. I would use calculations to test your results but this should work. Create a time calculation with:

Div(theTimeField ; 100)

If the User has typed 1400 and it produces 1400:00:00, the above calc will convert it to 14:00:00. Once you have verified you can get the results you wish, you don't need a script or to loop through your records at all. BACK UP FIRST and do this:

Go to your field definition on that time field. Change it from standard time field to calculation (result of TIME) and enter the above calc. Do not mess with Storage Options (don't make the calc unstored). Say OK to back clear out of field definitions. You will notice that time field has changed to 14:00:00.

Now go back into your field definition and change it back to a standard time field. The data will preserve the calculated reformat. This is a quick way to correct data by using FM instead of running a script. It is faster than Replace Contents, looping scripts and other such nonsense.

LaRetta

[Sergeant Ron]

I'll give it a go, thanks Laretta.