How to add a time value in string format to a number value

[gallobask]

I'm working in crystal 8.5. My database stores military time in a string format (13:15) I have another field that gives me estimated time in minutes (90)but the field is a number field not a time field. I need to add the two fields to get a time. 13:15 plus 90 minutes gives me 14:45.

I have tried converting the string to time (CTime) and get bad date time error.
I was able to convert the number field to minutes (90 minutes shows as 1:30) but when I try to add the two it just concatinates (13:151:30).

I saw formulas posted on the internet to convert to hours/minutes/seconds but don't understand how to use it with only hours and minutes in the string field.

I'm self taught crystal user with a clinical not technical background - learned by trial and error. There seems to be a wealth of brain power out there on this site!!! Can anybody help me with this mess!!! Thank you!!!!

 

[IanWaterman]

YOu can use Time function to convert your time string to true time.

time(timestring)

However that will not help as eventhough the function dateadd allows you to add minutes you need to start with a date.

If you can convert your time to date time, then you can add minutes.
eg

@datetime

datetime(year(currentdate), month(currentdate), day(currentdate), left(timestring,2), right(timestring, 2), 0)

Then use this in new formula

@newtime

Time(dateadd("n", estimatetimefield, @datetime))

Ian

 

[gallobask]

Thank you Ian. I tried the first formula
time({SYS_TIME.TIME_CON_STIME})and got error "bad time string

 

[IanWaterman]

As we are not using that its not really a problem. However, try

time(left({SYS_TIME.TIME_CON_STIME}, 2), right({SYS_TIME.TIME_CON_STIME}, 2), 0)

Ian

 

[gallobask]

Ok
time(left({SYS_TIME.TIME_CON_STIME}, 2), right({SYS_TIME.TIME_CON_STIME}, 2), 0) yields - "too many arguments given to this function"

 

[IanWaterman]

sorry for got it was a string

time(tonumber(left({SYS_TIME.TIME_CON_STIME}, 2)), tonumber(right({SYS_TIME.TIME_CON_STIME}, 2)), 0)

will need to change this one too

@datetime

datetime(year(currentdate), month(currentdate), day(currentdate), tonumber(left(timestring,2)), tonumber(right(timestring, 2)), 0)

Ian

 

[gallobask]

I tried time(tonumber(left({SYS_TIME.TIME_CON_STIME}, 2)), tonumber(right({SYS_TIME.TIME_CON_STIME}, 2)), 0)

still get "the string is non numeric"

Thanks for trying.....I appreciate it!

 

[IanWaterman]

YOu need to check what is in your time field. Your original post indicated (13:15)

You need to modify the left and right functions depending on what string is in the field.

Ian

 

[lbass]

It doesn't look like the type of field that could be null, but I wonder if that is the cases. You could try:

if isnull({SYS_TIME.TIME_CON_STIME}) or
trim({SYS_TIME.TIME_CON_STIME}) = "" then
time(0,0,0) else
time(tonumber(left({SYS_TIME.TIME_CON_STIME}, 2)), tonumber(right({SYS_TIME.TIME_CON_STIME}, 2)), 0)

Then in your other formula, you would want to check whether this formula = time(0,0,0) so that you can eliminate that row from the calculation if it does.

-LB

 

[gallobask]

I used if isnull({SYS_TIME.TIME_CON_STIME}) or
trim({SYS_TIME.TIME_CON_STIME}) = "" then
time(0,0,0) else
time(tonumber(left({SYS_TIME.TIME_CON_STIME}, 2)), tonumber(right({SYS_TIME.TIME_CON_STIME}, 2)), 0)And it seems to work!. It returns a time field. If the field is 15:57 it returns 3:57:00PM which is correct!!!

Thank you!!!