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
Files:
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.
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
Aveen, I will try it out at work this morning as I have 8.5 there and make any adjustments needed.
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.