Formula to add leading or trailing zeros to string 1

[ladyemrys]

Hi!

I'm new to these forums and hope i'm posting this to the right place. I'm working on a report using CR11 developers ed., that is designed to calculate the time difference between when a user logs on to the internet and when they log off. The problem is that the data is inconsistent. For example:

End Time:

9:9
04:2
4:20

Here is what i have tried to pad add a zero:

Right("0" + {Timers_Users.Finished_Time}, 5)

i'm having trouble getting the formula to recognize how many digits are to the right of the ":" and how many are to the left and then to pad the string accordingly. Any help is much appreciated!

 

[synapsevampire]

If you're trying to use it as a time why are you padding it at all, why not make a time out of it or convert it to seconds? What good is having a padded string if you need to perform calculations?

I suggest that future posts include technical information:

Database
Example data
Expected output

Your post doesn't how what you have or what you need, rather it states an architecture you feel must be used.

Anyway, here's some code that should help:

val(split({table.field},":")[1])

val(split({table.field},":")[2])

They will respectively give you the value of each entity, providing you always have both (a .

To convert it to a time, use:

time(val(split({table.field},":")[1]),val(split({table.field},":")[2]),0)

Or you might just convert it all to seconds:

((val(split({table.field},":")[1])*60)*60)+(val(split({table.field},":")[1])*60)

Hopefully this encompasses whatever it is that you actually need.

-k

 

[synapsevampire]

Sorry, messed up the seconds formula:

Or you might just convert it all to seconds:

((val(split({table.field},":")[1])*60)*60)+(val(split({table.field},":")[2])*60)

-k

 

[ladyemrys]

Thank you soo much! I'll try both of them. I didn't mean to sound like i had a structure i was imposing on anyone - i'm really new to programming and CR, and am open to anything! Next time i'll be sure to post more info as you requested

Thanks again for your help!

 

[ladyemrys]

My goodness, the time conversion is beautiful. I can't thank you enough for your help!

 

[synapsevampire]

Glad that it worked out.

Rather than typing thanks, Tek-Tips has a means to do so which is to click the thanks within your post, which supplies authors with stars and let's the world know that this post has substance.

Later we can redeem them for free greyhound bus tours of 7-11 dumpsters, and the like.

Always surprised at how many people have that blaring in their face yet miss it. I guess the fact that we're constantly inundated with mindless advertising makes us tune such things out.

-k

 

[ladyemrys]

Done! LOL although i wonder about the value of those dumpster tours *stifles a giggle* I'll remember to do both in the future!