Change Number field to Time field

[maggiev]

I am currently working in a CR Report. The time field shows as a number field such as 125044. I want this field to show as 12:50. I do not need the seconds. I am a new user and not very good with formulas.

I appreciate a little help.

Thanks,
Maggie

 

[Ngolem]

do you just want to display this as a time or actually use this as a time value??

To display this value in the format you want, I would ask you what you expect the value for 09:09 to look like??

would it be 90900 with 00 being the seconds??

In other words, I am not being garuanteed a 6 digit number, am I?

so to convert the number to a time display (not used for anything else) then the following formula should work

@DisplayTime

WhilePrintingRecords;

stringVar tempTime := totext({table.timeNum},0)

if length(tempTime) = 5 then
tempTime[1] + ":" + tempTime [2 to 3]
else
tempTime[1 to 2] + ":" + tempTime [3 to 4];


that should do it.





JimBroadbent@Hotmail.com

 

[synapsevampire]

I hope that your description is wrong, otherwise, I don't think that you can extract a time, for example if the field contains:

1121

This could be either:

1:12:1
or
1:1:21
or
11:2:1

I'll make the assumption that the field is a char (string), and that the precision is constant at 6 digits:

Create a formula and use:

left({Mytable.timestring},2)+":"+mid({Mytable.timestring},3,2})

(I'm going from memory here, so check the MID function if it fails)

This would produce from your example:

12:50

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[synapsevampire]

Ngolem:

Your solution assumes that the first character is always the hour with a precision of 5 digits, but it could be:

1:12:21

11:2:21

11:22:1

And what if the precision is 4 or 3 digits?

I think that Maggiev misunderstands the data, or the database architect will go back to waiting tables soon

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[maggiev]

To clarify my problem the time field in the DB shows as a a Number.

For example 7:59:11 shows in the DB as 75,911.00 When I turn this number field w/o decimals it shows as 75911.

If the time is 13:06 it shows in the field as 130,621.00 When I turn this to a number w/o decimals is 130621.

This is a time display that shows when the record was originally created.

Thanks for the replies above. I will try some of those formulas.

Thank you,
Maggie

 

[Ngolem]

I think I covered both bases in my answer...as long as the seconds are the rightmost 2 digits and the 2 digits to the right of that are the minutes with the balance (1 or 2 digits) being the hour.

If this isn't the case then it would be impossible to decipher the number...I am also assuming no commas for the thousands place. JimBroadbent@Hotmail.com

 

[synapsevampire]

Now it makes sense, your assumption was dead on, Ngolem.

Silly me...

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Ngolem]

No problem ...thanks for checking up on me ...my logic isn't perfect all the time... JimBroadbent@Hotmail.com

 

[maggiev]

Thanks Ngolem! it worked like a charm.

Maggiev