Converting from a number to time.

[wil1952]

I am trying to convert a number field to a time field. In my database there is a visit.begin_time field and a visit.end_time field they are both number fields. I need to be able to get total time between the begin and end times in an HH:MM format for each visit then total time for each day. Any help is greatly appreciated.

 

[Turkbear]

Hi,
What is the format of the number field?
What do the numbers represent?
What database? Version?
Which Crystal version?

More info please...




To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[wil1952]

NNNN

Military Time

Informix Database

9.5

 

[lbass]

You need to show samples of the field along with the times they represent.

-LB

 

[wil1952]

visit.begin_time = 1440
Visit.end_time = 1506

Although the visit are showing up in a military time formart they are number fields.

When you try to show the amount of time spent on the visit if you subtract one from the other you get 66 but it should be 26 minutes.

 

[lbass]

Try converting each field to minutes using:

val(right(totext({table.number},0,""),2)) +
(val(left(totext({table.number},0,""),2))*60)

Then subtract one formula from the other. You can then convert back to HH:MM format if you wish.

-LB

 

[wil1952]

Thanks that worked. But it has now presented another problem. When the time is a number such as 0930 it is leaving off the 0 on the front end so when it separates out the left two digits and multiplies by 60 it is multiplying 98 * 60 instead of 9. Also, once I have total all minutes at the end of the report how can I convert it back to HH:MM.

 

[lbass]

Sorry. Change that to:

val(right(totext({table.number},"0000"),2)) +
(val(left(totext({table.number},"0000"),2))*60)

Then to convert your diff back to HH:MM use:

totext({@diff}/60,"00")+":"+totext(remainder({@diff},60),"00")

-LB

 

[wil1952]

Thanks for the help it is working great now. Hope you have a Merry Christmas this was a gift for me.