Technical Article
Maximising your use of IDEA – The @Functions
Regular readers of the AuditWare newsletter will be aware that I have been writing a series of articles on the @Functions in IDEA.
Usually in these articles I concentrate on one @function but with this I am going to use a few, these are:
@val() Turns a character expression into a number
@mid() My favourite function. Extracts a portion of text from within a field
@int() Takes only the integer value (the bit in front of the decimal point)
> of a number
@str() Turns a number into a character.
Modulus.
> That enigmatic button in the equation editor that looks like this:
>		 This calculation gives you the remainder of a division e.g. 7%3=1.
So what am I going to use all of these functions for?.....

Performing calculations with time fields that are formatted as a character with the time in the format hh:mm:ss.
For example you may have two time fields, time logged on, and time logged off and want to work out exactly how long the user was logged on.
Many users have said to me ‘you can’t do that in IDEA as there is no function for it so we do it in Excel’.
As a real old school, dyed in the wool computer auditor I shudder whenever the “E” word is used so have searched for a way of doing it in IDEA.
The whole process looks quite daunting but essentially there are three steps:
  1. Create virtual fields holding the time fields as the number of seconds from midnight.
  2. Perform the calculation required
  3. Create a virtual field to hold the time elapsed in the format hh:mm:ss
Convert time fields into numbers
The basic idea is to take each part of the field i.e. hours, minutes and seconds and then multiply each part by the number of seconds
(3600 for hours, 60 for minutes and 1 for seconds) and then add them together.
> The equation to do this for the hours part of the field is:
@val(@mid(time_field,1,2)) * 3600
Extracts the two digits from the time_field that represent the hours
Converts the hours into a number, multiplies the number of hours by the number of
> seconds in each hour to calculate the total number of seconds.
This process is repeated for the minutes and seconds part of the field,
> obviously replacing the start parameter in the @mid function and the multiplier.
The full equation to calculate the total number of seconds (from midnight) would therefore be:
(@val(@mid(time_field,1,2))*3600)+(@val(@mid(time_field,4,2))*60)+@val(@mid(time_field,7,2))
If one had a time of 10:24:36 this would convert into (10 * 3600) + (24 * 60) + 36 = 37476
Perform calculation
This is the quite simple matter of subtracting one of the calculated time fields from the other,
> creating a field called time_diff.
Convert the time elapsed to the format hh:mm:ss
OK, being totally honest this is the really nasty part of the process so please stay with me on this.
This involves calculating how many whole hours, whole minutes and seconds there are.
> And then concatenating them together with some colons (:) to get something that looks like hh:mm:ss.
This is best explained in steps
Explanation Example
First calculate the whole number of hours and turn that into a character.
> This is done by taking the integer value of the time difference divided by the number of seconds in an hour (3600).
> The equation would be:
> @str(@int(time_diff/3600),2,0)
> Calculates the number of hours
> Takes the integer value i.e. the complete hours
> Converts the number into a character		 Assume a time difference value of 19653
> 19653/3600 = 5.4591
> 5
> “5”
Next the remaining part of the field i.e. that less than an hour is converted into the whole number of minutes. This is done by taking the remainder of the time_diff field divided by the number of seconds in an hour and calculating the whole number of minutes in that. This is done using the following equation:
> @str(@int((time_diff % 3600) / 60),2,0)
> Calculates the remaining time after the whole hours have been removed
> Calculates the number of minutes
> Takes the integer value i.e, the whole number of minutes
> Converts the number into a character		 19653 % 3600 = 1653
> 1653 / 60 = 27.55
> 27
> “27”
Following this the remaining part of the field i.e. the seconds is calculated and turned
> into a character. This is achieved by taking the remaining number after the whole hours and whole minutes have been removed. The equation for this is:
> str(((time_diff % 3600) % 60),2,0)
> Calculates the remaining seconds after removing whole hours
> Calculates the remaining seconds after whole minutes have then been removed
> Converts the number into a character		 19653 % 3600 = 1653
> 1653 % 60 = 33
> “33”
Finally the separate components are concatenated with a colon (:) inserted between each them. Character strings are concatenated by quite simply using the plus sign (+). 5:27:33
The whole equation to perform this task is thus:

@str(@int(time_diff/3600),2,0)+“:”+@str(@int((time_diff%3600)/60),2,0)+“:”
> +@str(((time_diff%3600)%60),2,0)
Here’s one for the equations library
Now I know some of you do not like to create lots of virtual fields so would like the ability to do it all in one step.
If you do, and it’s not compulsory, you can copy this equation and just replace field names as necessary.
In the equation Time_1 is the start time and Time_2 is the finish time - Good Luck!
@STR(@INT((((@VAL(@MID(TIME_2,1,2))*3600)+(@VAL(@MID(TIME_2,4,2))*60)
> +@VAL(@MID(TIME_2,7,2)))-((@VAL(@MID(TIME_1,1,2))*3600)+(@VAL(@MID(TIME_1,4,2))*60)
> +@VAL(@MID(TIME_1,7,2))))/3600),2,0+":"+@STR(@INT(((((@VAL(@MID(TIME_2,1,2))*3600)
> +(@VAL(@MID(TIME_2,4,2))*60)+@VAL(@MID(TIME_2,7,2)))-((@VAL(@MID(TIME_1,1,2))*3600)
> +(@VAL(@MID(TIME_1,4,2))*60)+@VAL(@MID(TIME_1,7,2))))%3600)/60),2,0)+":"
> +@str((((((@VAL(@MID(TIME_2,1,2))*3600)+(@VAL(@MID(TIME_2,4,2))*60)
> +@VAL(@MID(TIME_2,7,2)))-((@VAL(@MID(TIME_1,1,2))*3600)
> +(@VAL(@MID(TIME_1,4,2))*60)+@VAL(@MID(TIME_1,7,2))))%3600)%60),2,0)
 
AuditWare Systems Limited:
> The Old Sawmills, Eridge Road, Eridge Green, Tunbridge Wells, Kent, TN3 9JR
> Telephone: + 44 (0) 1892 512348 / Fax: + 44 (0) 1892 512342