a calculation monster

[stefangs]

i thought i'd share with you how i spent my last couple of hours. this is a calc (result = time) which, for simple entry, converts a number into a time with proper separaters, observing plus/minus. i'm overlaying the entry with the calc which is set to not allow entry, so on exit you see the format filtered result. the number field is validated to allow values between -235959 and 235959. for example:

5 becomes 0:00:05

-324 becomes -0:03:24

this makes for nice data entry and it works, but in case i didn't see the forest for the trees, perhaps one of you gurus can think of a less convoluted way of accomplishing the same thing.

Cnv is the number field to be converted.

If(Cnv > 0,

Case(

Length(NumToText(Cnv))<3, Time(0, 0, Cnv),

Length(NumToText(Cnv))=3, Time(0, Left(NumToText(Cnv), 1), Right(NumToText(Cnv), 2)),

Length(NumToText(Cnv))=4, Time(0, Left(NumToText(Cnv), 2), Right(NumToText(Cnv), 2)),

Length(NumToText(Cnv))=5, Time(Left(NumToText(Cnv), 1), Middle(NumToText(Cnv), 2,2), Right(NumToText(Cnv), 2)),

Length(NumToText(Cnv))=6, Time(Left(NumToText(Cnv), 2), Middle(NumToText(Cnv), 3,2), Right(NumToText(Cnv), 2)))

,

Case(

Length(NumToText(Cnv))<3, Time(0, 0, Cnv),

Length(NumToText(Cnv))=3, Time(0, 0, Right(NumToText(Cnv), 2)) * -1,

Length(NumToText(Cnv))=4, Time(0, Middle(NumToText(Cnv), 2, 1), Right(NumToText(Cnv), 2)) * -1,

Length(NumToText(Cnv))=5, Time(0, Middle(NumToText(Cnv), 2, 2), Right(NumToText(Cnv), 2)) * -1,

Length(NumToText(Cnv))=6, Time(Middle(NumToText(Cnv), 2, 1), Middle(NumToText(Cnv), 3, 2), Right(NumToText(Cnv), 2)) * -1,

Length(NumToText(Cnv))=7, Time(Middle(NumToText(Cnv), 2, 2), Middle(NumToText(Cnv), 4, 2), Right(NumToText(Cnv), 2)) * -1

)

)

[kennedy]

I might shorten it to:

If(Cnv < 0, "-", "") & NumToText(Int(Abs(Cnv)/10000)) & ":"

& Right("0" & NumToText(Mod(Int(Abs(Cnv)/100), 100)), 2) & ":"

& Right("0" & NumToText(Mod(Abs(Cnv), 100)), 2)

Not quite such a monster.

[ldebondt]

How can this be converted to show 0800 as "8:00 am" or 1435 as "2:35 pm"

[kennedy]

Much shorter... but a bit uglier:

NumToText(Mod(Int(Cnv/100)+11, 12)+1) & ":" & Right("0" & NumToText(Mod(Cnv, 100)), 2) & If((Cnv / 100) < 12, " am", " pm")

HTH.

[stefangs]

wow! that's short and sweet, but... not quite what i meant. i wondered why your calc was giving me the wrong result until i realized that you are assuming an entry in seconds. what i'm trying to do is assume an entry as minutes/seconds so for example:

50 becomes 0:00:50, but

500 becomes 0:05:00

5000 becomes 0:50:00

in other words, the last two digits are always seconds, before that are minutes, before that are hours. i know it's gory, but hey, i've got a working calc, so it's ok if it can't be done shorter. thanks for going over it!

[The Bridge]

Does this do what you need?

TextToTime(Substitute(NumToText(Sign(TextToNum(Time_Text))), "1", "") & Replace(Replace(Right("000000" & Abs(TextToNum(Time_Text)), 6), 5, 0, ":"), 3, 0, ":"))

returns a time format.

[kennedy]

Whatchu talkin bout, man!!

I just cut'n'pasted directly out of my note above and put in exactly the inputs you suggested in both your notes and got out exactly the results you asked for. If you're getting the wrong answer, try cutting and pasting exactly what I have above.

I am not assuming seconds... for that I'd be mod'ing and dividing by 60, not 100.

[The Bridge]

Just realized your original question called for converting a number to a time. Here's a modified version of my previously-posted calc with this in mind:

TextToTime(Substitute(NumToText(Sign(Cnv)), "1", "") & Replace(Replace(Right("000000" & NumToText(Abs(Cnv)), 6), 5, 0, ":"), 3, 0, ":"))

[stefangs]

hi kennedy,

i couldn't tell for sure if your post sounded angry - well i hope not, otherwise please accept my apology.

i did copy/paste your formula directly, but when i enter, say 150, your formula returns 0:02:30 when i was hoping for 0:01:50.

150 seconds equals, of course, 2 minutes/30 seconds. i guess you could say that i'm asking for a 'wrong' result. not sure if it's just a communication thing. thank you anyway.

[stefangs]

hi peter,

yup, that one does it! it's a bit over my head, so i'm still picking it apart trying to understand. it even seems to work whether the input is a number or text field. thanks much!!

[kennedy]

i did copy/paste your formula directly, but when i enter, say 150, your formula returns 0:02:30 when i was hoping for 0:01:50.

Well, for me it returns 0:01:50... just as you wanted. There is no way the math I gave you above could return 0:02:30 for an input of 150. Are you sure you're actually using my calculation, and not another one you already had in there?

There is no communication problem... I understand and am getting exactly what you're asking for with that calculation above. Try creating an empty database... add a *single* field Cnv that is a Number... add a second field CnvAsTime that is a calculation... cut'n'paste from my note above. Put the two fields on a layout... I guarantee it'll give you exactly what you're looking for.

[kennedy]

Just realized your original question called for converting a number to a time.

Oh, I thought he wanted to do the formatting by hand, not using standard Time formatting... I thought that would be "cheating"... converting to Time can be much shorter and simpler:

3600 * Int(Cnv/10000) + 60 * Mod(Int(Cnv/100), 100) + Mod(Cnv, 100)

Since we've been having difficulty with cut n paste, I have attached an FP5 with my first formula, The Bridge's formula that inserts ":"s, and my new formula that simply converts from 100 base to seconds and lets FP's Time type handle the rest.

The records contain each of your test cases in this thread showing that all three formulations return the result you're asking for.

Enjoy.

CnvAsTime.fp5.zip

[stefangs]

ah, now i'm with you!! i was expecting the calc result to be 'time' and had it set as such, but it needs to be text!! so, yes, now your calc works perfectly well. sorry about the confusion and thanks for your perseverance.

[RussBaker]

Have a look at the file I put up in the samples section of this forum - under my Handy Bits heading. The file name is RussCalcs From Oz.

It has a whole lot of time formats calcs in it, under its Time Tab

[stefangs]

that's impressively short and sweet! i knew it had to be possible to do it shorter than my original monster, but i didn't think it could be done in so few chars. thank you all!