Converting Times

Hi Steve and welcome to the site.

If you have version 9 you can use the following equation in the equation editor:

@Ntot(@if(SUB_ADD = "+", @Tton(TIME) + HOURS * 60*60 + MINUTES*60, @Tton(TIME) - HOURS * 60*60 - MINUTES *60))

This won't work in V8 as the @NtoT and the @TtoN function did not exist.  Let me know if this solves your problem, if not I can probably do something with a custom function to help you out.

Also what do you expect if you have a time like 02:00:00 - 4 hours?  Do you expect 0, a minus or does the clock loop around and give you 22:00:00?  What about 22:00:00 + 4 hours do you expect 26:00:00 or 02:00:00?

In the equation above it gives an error if it is 0 and if it goes over 24 hours it just keeps increasing.

So let me know and I will try and help you out.

Hi Steve, I read your message a bit closer and realized what you wanted. As there is a bit of logic there I decided to do it as a custom function, it could probably be done as an equation but the equation editor isn't the best when you have lots of logic to check, fortunately we have options to create a custom function. So below is the code and I have attached the custom function for you to try. Unfortunately custom functions don't handle time fields so the input and the output have to be a character field. The function takes four variables, namely the time, a + / -, the number of hours and the number of seconds. It doesn't do any checking to see if you put something in like increasing by 100 hours, in which case you will get a strange answer, the range I expect for hours is 1 to 23 and 1 to 59 for minutes. So try it out and let me know if this is what you are looking for. It also should work for both V9 and V8.

I have updated the function so that if you do not put in a + or - it will default to a + and if the hours or minutes are greater than 60 it defaults to 60 and less than 0 it defaults to 0.

'*******************************************************************************************************
' Custom Function: LocalTime
' Author: Brian Element - brian.element@ideascripting.com
' Date: Feb 4, 2013
' Purpose: To calculate the local time based on a difference located in an hours and minutes field
' This custom function is presented as is without any warranty or guarantee.  Anybody using this
' custom function is encouraged to validate the effectiveness and reliablity on their own.
'*******************************************************************************************************

Option Explicit
Function LocalTime(TimeField As String,AddSub As String,Hours As Double,Minutes As Double) As String
	Dim iHours As Integer
	Dim iMinutes As Integer
	Dim iSeconds As Integer
	Dim sTempTime As String
	
	'break the time into hours, minutes and seconds.  Seconds stay as a string as nothing is done with them.
	iHours = Mid(TimeField, 1, 2)
	iMinutes = Mid(TimeField, 4, 2)
	iSeconds = Mid(TimeField, 7, 2)
	
	'check to see if you are adding or subtacting time, then update the hours and minutes based on the fields.
	If AddSub = "+" Then
		iHours = iHours + Hours
		iMinutes = iMinutes + Minutes
	Else
		iHours = iHours - Hours
		iMinutes = iMinutes - Minutes
	End If
	
	'if minutes is greater and 59 then add an hour and subtract 60 minutes
	If iMinutes > 59 Then
		iHours = iHours + 1
		iMinutes = iMinutes - 60
	End If
	
	'if minutes are less than 0 then subtract an hour and add 60 minutes
	If iMinutes < 0 Then
		iHours = iHours - 1
		iMinutes = iMinutes + 60 
	End If
	
	'if hours are larger than 23 then subtract 24 to have the clock "turn around"
	If iHours > 23 Then
		iHours = iHours - 24
	End If
	
	'if hours are less than 0 then add 24 hours to have the clock "turn around.
	If iHours < 0 Then
		iHours = iHours + 24
	End If
	
	'check for the length of hours and minutes and if less than 10 then add a"0" as the first digit.
	If iHours < 10 Then
		sTempTime = "0" & iHours & ":"
	Else
		sTemptime = iHours & ":"
	End If
	If iMinutes < 10 Then
		sTemptime = sTemptime & "0" & iMinutes & ":"
	Else
		sTemptime = sTemptime  & iMinutes & ":"
	End If
	If iSeconds < 10 Then
		localtime = sTemptime & "0" & iSeconds
	Else
		localtime = sTemptime & iSeconds
	End If
	
End Function

Thank you Brian. I do have v8.
As I said I'm relatively new to IDEAScript. How do I use the function to append a new field to the original database?
Thanks

Hi Steve, well what I did is not technically IDEAScript, it is something you can do through the equation editor which based on what you were asking for I think works best.

So here are the steps to use the custom function:

• Open the database you want to change.
• In the database double click on any of the cells to open the Field Manipulation dialog
• On the right side select the Append button
• Give the new field a name, the type should be Virtual Character (or Character) and the length should be 10
• Double click on the Parameter field to open the equation editor
• These steps you only need to do once to import the LocalTime custome function
• Find the # button on the top and click it which will open the Custom Functions dialog
• Select the Import Button
• Locate the LocalTime.ideafunc that you downloaded from the previous post
• Click on open
• Close the Custom Functions dialog, you need only do this step once as the Custom Function will now be available to you on this computer
• In the equation editors type #LocalTime (IDEA functions start with @ and Custom functions start with #)
• Enter your parameters so you would have #LocalTime(TimeFiled, "+", 6, 30) this will increase your time field by 6 hours and 30 minutes.
• Select the check mark to validate and close the Equation Editor
• Click OK button to add the field to your database.

Let me know if this makes sense or if you have any problems with it.

Thank you Brian! It works great and your description was easy to follow :-)
Thanks again and have a nice day:-)

Hi Steve, great to hear it worked.  Let me know if there is anything else I can help out with.

Hi Brian,
I'm trying to automize the execution with an IDEA Script. I don't have a problem with using the custom function, but prior to that I have to convert the Time Field I have to a Character Field. If I do that manually with the Field Manipulation Dialog everything works fine, but if I do it with IDEAScript I randomly get either an empty field or fields with an Error in it, but never the correct String (e.g. "08:00:00").
Here is my Code (I commented most of it so that nothing else could lead to that behaviour):
 
<code>
Dim ThisTable As Table
Dim field As Field
 
Set db = Client.OpenDatabase(dbname)
Set task = db.TableManagement
Set ThisTable = db.TableDef
Set field = ThisTable.GetField("TIME_OF_ENTRY")
 
field.Type = WI_VIRT_CHAR
field.Length = 10
 
db.CommitDatabase
 
'Set field = ThisTable.NewField
'field.Name = "LocalTime"
'field.Description = ""
'field.Type = WI_CHAR_FIELD
'field.Equation =  "#LocalTime(#LocalTime( TIME_OF_ENTRY ; ""-"" ;1;0); SUB_ADD ; HOURS ; MINUTES )"
'field.Length = 8
'task.AppendField field
 
'task.PerformTask
 
Client.CloseAll
Set field = Nothing
Set ThisTable = Nothing
Set task = Nothing
Set db = Nothing
 
Exit Function
</code>
 
The table is imported automatically with the use of a .rdm-File. I could change it so that the field would be imported as a Character Field, but I would rather not do that, because I may need it to be a Time field, when I implement new functions in the future
 
Steve
 

Hi Steve, the problem is, at least as far as I know and I can't say I have tested it much, is that you can't access custom functions through IDEAScript as the custom functions sit outside of IDEA.

As you want to do this by script I can put something together for you.  Now are your fields hard coded or do they change based on the file?  I am just wondering if you need a dialog to select the fields or are the fields always the same name so that you can skip this step?  Also for the hours and the minutes and + or - is this alwasy the same for the entire file or different depending on the record?

If I have time I will put something together for you tonight, if not it will go to the weekend.

Hi Brian, the custom function is not the problem, I can call it from IdeaScript without problems.
The thing is, that I have this Field "TIME_OF_ENTRY" which has the type "Time". But for the custom function I need it to be a Character field. As you've said in one of the previous posts custom functions will only work with character fields.
 
So what I'm doing manually is:
Open the database,
click on data,
click on field manipulation,
select the Field TIME_OF_ENTRY
and change the type from "Time" to "Character*".
This will work fine.
 
However if I try to do the same thing in IdeaScript(with the code of the previous post) it results in empty values or Error values in the "TIME_OF_ENTRY" field.
 
I know that this is not a problem of calling the custom function from IdeaScript, because I tried it with manual converting the Type to character and then calling the custom function and it worked fine.
 
Many thanks in advance:-) 

Steve you will have to do the script in two steps. 1 is what you are doing and the second step I have the code below. This will change the character field into a time field for you. Just change TEST for your field name which I believe would be "TIME_OF_ENTRY", also in your code don't create a virtual character field but a character field instead, so in your code change WI_VIRT_CHAR to WI_CHAR_FIELD and it should work. Unfortunately I haven't had time to test this out so let me know if it doesn't work. Thanks

Set db = Client.OpenDatabase("time test-Sheet1.IMD")
Set task = db.TableManagement
Set table = db.TableDef
Set field = table.NewField
field.Name = "TEST"
field.Description = ""
field.Type = WI_TIME_FIELD
field.Equation = "HH:MM:SS"

task.ReplaceField "TEST", field
task.PerformTask
Set task = Nothing
Set db = Nothing
Set table = Nothing
Set field = Nothing