Changing the format of a field

[Former Member]

I have added multiple Time fields(Date Time Format) and have stored it as Total hours(String format).When i am converting the Total hours to time format using the CTime() function,i am getting an error as "Bad Time format String".Please suggest me how can i convert the Total Hours field as a time data type field so that i can perform calculation on it

[Former Member · Accepted solution]

Hi Arun,

what is the format of the string you are using in Ctime() function. can you add a screen shot of your report?

--Naga.

[Former Member · Accepted solution]

The format of the String is hh:mm:ss and i have calculated by the following code :

1. Convert all the time fields to a common time

unit such as seconds

2. Calculate the total time in seconds

3. Convert the seconds back to hh:mm:ss format.

Convert time fields to seconds

-----------------------------

Convert the time fields to seconds so that a

common time unit is used to sum up all three

different time units.

If your field is a datetime field, complete the

following steps:

1. Create a new formula and call it

@ConvertTimeToSeconds

2. To convert the datetime field to seconds,

create a formula similar to the following:

local numbervar hours;

local numbervar minutes;

local numbervar seconds;

// Convert the hours to seconds by multiplying by

// 3600

hours := hour({@time}) * 3600;

// Convert the minutes to seconds by multiplying by

// 60

minutes := minute({@time}) * 60;

seconds := second({@time});

//add up all the seconds

hours + minutes + seconds;

Create a summary formula that will sum the

@ConvertTimeToSeconds

sum(@ConvertTimeToSeconds, Group)

Convert the seconds back to hh:mm:ss format

---------------------------------------------

Create a formula that converts the @TotalSeconds

results back to hh:mm:ss format.

1. Create a new formula and call it

@ConvertTotalSeconds

2. To convert the results from @TotalSeconds back

to hh:mm:ss format, create a formula similar to the

following:

local numbervar RemainingSeconds;

local numbervar Hours ;

local numbervar Minutes;

local numbervar Seconds;

//divide the @TotalSeconds by 3600 to calculate

// hours. Use truncate to remove the decimal portion.

Hours := truncate({@TotalSeconds} / 3600);

// Subtract the hours portion to get RemainingSeconds

RemainingSeconds := {@TotalSeconds} - (Hours *

3600);

// Divide RemainingSeconds by 60 to get minutes.

// Use truncate to remove the decimal portion.

Minutes := truncate(RemainingSeconds/60);

// Subtract the Hours and Minutes and what is left

// over is seconds.

Seconds := {@Totalseconds} - (Hours * 3600) -

(Minutes * 60);

// Format the hours, minutes, and seconds to hh:mm:ss

totext(Hours,"00") + ":" + totext(Minutes,"00") +

":" + totext(Seconds,"00")

[abhilash_kumar · Accepted solution]

Hi Arun,

Please paste the code where you get the error too.

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

This is the code for which i am getting the error

CTime({@ConvertTotalSeconds})

[abhilash_kumar · Accepted solution]

Why do you need this formula?

The @ConvertTotalSeconds formula is already converting seconds to hh:mm:ss format.

-Abhilash

[Former Member · Accepted solution]

Yes it's converting but this is converting this to a string format but i want the format as time format so that i can perform some calculation on it

[abhilash_kumar · Accepted solution]

Change the last line of code in the ({@ConvertTotalSeconds}) formula to:

Ctime(hours, minutes, seconds)

-Abhilash

[Former Member · Accepted solution]

it works when the total hours is less than 23 but when it exceed 23 then it shows an error that the range should be between 0 to 23 and i agree to that.Basically my intension was that i have three fields named as

Allocated Hous(String format)

Total Hours reported (String format)

Total hours missing

I have got the Allocated hours and the Total Hours reported and now i have to find the Total hours missing which is the difference between the Allocated hours and the Total Hours reported fileds (Allocated hours-Total Hours reported).

So how can i find the Total hours missing.Suggest me some formula for that.

Thanks