Assistance with Time Data

aveenm293

Forums

Hi Brian
I am struggling with converting a piece of data. I have attached a spreadsheet with a few records. I am trying to change the data in the Column "Open time" Currently it represents 12:00A. This time is in the morning and I need to convert it to 12:00:00. I am not concerned with the seconds so it can always read 00.
For the data in row 8 i.e. "04:21P" it is in the afternoon and must read 16:21:00. Again the seconds are not important so they will always be 00. Is the there a way to convert in IDEA?
Thank you in advance!
Aveen
 

Brian Element Thu, 07/11/2013 - 17:33

Hi Aveen,

Try this out, create a virtual time field and paste in the following equation:

@Ctot(@If(@Mid(FIELD, 6, 1) = "A", @Mid(FIELD,1,5), @str(@Val(@Mid(FIELD,1,2)) + 12, 2, 0) + @Mid(FIELD, 3,3 )), "HH:MM")

I couldn't remember if you use 8.5 or 9 so I created the above that should work in 8.5.  There are more time related functions in V9 that would have made this easier but it works.  I am testing this on V9 so if you have any problems let me know.  Just place your Field where FIELD is indicated.

aveenm293 Fri, 07/12/2013 - 02:43

Hi Brain
Thank you very much. I am using IDEA 8.5. I have used the equation but I get a syntax error. I don't think there is a function called Ctot in v8.5.
Regards
Aveen
 

Brian Element Fri, 07/12/2013 - 07:30

Hi Aveen,

If you want to keep the time field as a character field you can use this equation:

@if(@Mid(OPEN_TIME, 6, 1) = "A", @Mid(OPEN_TIME, 1,5), @Str(@val(@mid(OPEN_TIME, 1, 2)) + 12, 2, 0) + @Mid(OPEN_TIME, 3, 3))

Unfortunately in V8 you cannot create a vitural time field (you can do this in V9).  So if you want the field as a time field you will have to export the file into as a text delimited format and then reimport it into IDEA, when importing you would define the new field as a time field with a mask of HH:MM.

If you are doing lots of work with Time fields I would recommend upgrading to IDEA 9 as it has greater functionality around time fields.