Convert number to a time without a custom function?

[skingjack2]

Hello,

I was wondering if this could possibly be done without using a custom function such as "(timemask(textfield); an example would be that an input would be "0305" which would be outputted as "03:05AM". Due the absence of owning a copy of Filemaker advanced edition, the custom function is not currently an option.

TIA.

Jack

[Ocean West]

This is just a let statement and can be used in any calc box just insert your text field

/* 7:00 AM to 6:59 PM can be entered as single numbers with assumed AM and PM. 7 = 7:00 AM, 1030 = 10:30 AM *, 1030p = 10:30 PM, etc.*/



Let ( 

raw = GetAsNumber ( textField )

 ;



Let ( inter = 

GetAsNumber (

Case ( Length ( raw ) = 1 ; raw & "00" ;

Case ( Length ( raw ) = 2 ; raw & "00" ;

Case ( Length ( raw ) = 3 ; raw ;

Case ( Length ( raw ) = 4 ; raw ))))

) ;



Let ( suffix = 

Case ( Right ( textField ; 2 ) = "AM" ; " AM" ;

Case ( Right ( textField ; 2 ) = "PM" ; " PM" ;

Case ( Right ( textField ; 1 ) = "a" ; " AM" ;

Case ( Right ( textField ; 1 ) = "p" ; " PM" 

)))) ;



Case ( inter > 0 and inter 

[Søren Dyhr]

There is an old one of Comment's trickeries doing exactly this, if you're prepared to lean against the timeformatting the layoutrendering provides:

Let(tt = GetAsText(Self);

Case(

Length( tt ) < 5;

Choose(

Length( tt ); "";

Time( 0; Int( tt ); 0 );

Time( 0; Int( tt ); 0 );

Time( Int( Left( tt; 1 ) ); Int( Right( tt; 2 ) ); 0 )

Time( Int( Left( tt; 2 ) ); Int( Right( tt; 2 ) ); 0 ));

Time(

Int( Left( tt; 2 ) );

Int( Left( Middle( tt; 3; 2 ) & 0; 2 ) );

0 )))

Stuffed inside the fields autoentering with the update set on!

--sd

test.zip

[skingjack2]

Ocean West,

Not quite sure I would phrase this as "just" another let statement; there is a lot to it (and to learn from) for a "slightly more" than a beginner user.

FYI, I copied the information substituting my text field name (Arrive_Time) for textField. Every attempt was met with an error message "cannot find specified field" in reference to this portion

of the calculation: Case ( inter > 0 [color:red]and inter < 700. Any further thoughts on what I may be overlooking?

Jack

Edited March 19, 2010 by Guest

[Søren Dyhr]

"cannot find specified field" in reference to this portion

of the calculation: Case ( inter > 0 and inter < 700. Any further thoughts on what I may be overlooking?

That... it must be a typo somewhere, since inter is a declared variable, with scope just inside the function!

If you give it a whirl through Debi Fuchs java-thingy would it look like this:

/* 7:00 AM to 6:59 PM can be entered as single numbers with assumed AM and PM. 7 = 7:00 AM, 1030 = 10:30 AM *, 1030p = 10:30 PM, etc.*/

Let(

    raw = GetAsNumber( textField );

    Let(

        inter =

        GetAsNumber(

            Case(

                Length( raw ) = 1;

                raw & "00";

                Case(

                    Length( raw ) = 2;

                    raw & "00";

                    Case( Length( raw ) = 3; raw; Case( Length( raw ) = 4; raw ) )

                )

            )

        );

        Let(

            suffix =

            Case(

                Right( textField; 2 ) = "AM";

                " AM";

                Case(

                    Right( textField; 2 ) = "PM";

                    " PM";

                    Case(

                        Right( textField; 1 ) = "a";

                        " AM";

                        Case( Right( textField; 1 ) = "p"; " PM" )

                    )

                )

            );

            Case(

                inter > 0 and inter < 700;

                Left( inter; 1 ) & ":" & Right( inter; 2 ) &

                If( not IsEmpty( suffix ); suffix; " PM" );

                Case(

                    inter ≥ 700 and inter < 1000;

                    Left( inter; 1 ) & ":" & Right( inter; 2 ) &

                    If( not IsEmpty( suffix ); suffix; " AM" );

                    Case(

                        inter ≥ 1000 and inter < 1200;

                        Left( inter; 2 ) & ":" & Right( inter; 2 ) &

                        If( not IsEmpty( suffix ); suffix; " AM" );

                        Case(

                            inter ≥ 1200 and inter < 1260;

                            Left( inter; 2 ) & ":" & Right( inter; 2 ) &

                            If( not IsEmpty( suffix ); suffix; " PM" )

                        )

                    )

                )

            )

        )

    )

)

and there is nothing dangling unfinished!

--sd

[Søren Dyhr]

Alright I accidentally ignored the legacy it build upon, now the function as such isn't recursive so it could be used as is from Dunning's site:

But is the calc' particular crafty??? The same thing could at least be boiled down to:

Let(

    [

        tt =

        Int(

            Substitute(

                Choose( Length( Self ) - 1; "ç00"; "ç00"; "ç"; "ç" );

                "ç";

                self

            )

        );

        ts = Case( Div( tt - 100; 1200 ); 100 + Mod( tt - 100; 1200 ); tt )

    ];

    Replace( ts; Length( ts ) - 1; 0; ":" ) & " " &

    Case(

        Position( Lower( Self ); "p"; 1; 1 );

        "P";

        Div( tt - 100; 1200 );

        "P";

        "A"

    ) & "M"

)

--sd

secondTest.zip

Edited March 19, 2010 by Guest

[skingjack2]

Soren & OceanWest,

Thank you both for your solutions; I was able to extend this

to one of the required fields; I'm now going to try to extend this calculation for use in another field. We'll see how this works out.

Jack

[Søren Dyhr]

I'm slightly baffled from the idea, as to why keeping things in textfields makes sense?? Could you expand please??

--sd

[skingjack2]

Sorry Soren,

I think my comment may have lacked clarity; the provided solutions work (with 1 minor hiccup- the field on the layout shows 12:00 -rather than being blank- until data is entered into the field & the calculation is executed).

Some background info may help:

Using an aviation example, a flight leaves the airport ("depart time") as well as arrives at destination (arrival time"). To ease user input friendliness, it was expected that the user could just enter the departure time as a number i.e.0335 and then it would convert to the equivalent time i.e. 03:35am. Your solutions accomplish this (with the aforementioned issue still needing to be remedied). The "arrival time" is more complex; how to extend the calculation to include the possibility of the flight arriving the following day (or even 2 days) later than the original departure date/time. Unfortunately no idea with respect to a suitable calculation has presented itself to me (some sort of a date/time calc but calcs are not my forte). I'm thinking my best/easiest option would be to add a popup field (" + 1 day", "+ 2 days") on the layout after the "arrival time" field that the user would have to select if appropriate. Not elegant but workable.

Sorry for the long winded explanation; hope it clarifies my intentions.

Jack

[Søren Dyhr]

Ah! I think you have misread my description somewhere, you have done this!

The upper removal of the check is wrong!I would say it's rather rare you should change the default behaviour!

I'm thinking my best/easiest option would be to add a popup field (" + 1 day", "+ 2 days") on the layout after the "arrival time" field

I just teste what happened if I typed 2500 into my latest template, and it seems to behave, but when the straw that breaks the camels back is reached is at bit in the haze for the moment, although I might be able to think it up ... is the suggestion much better. Unless you need to calculate the duration as well.

But by and large do you still owe me an explanation as to why you wouldn't solve this with the fieldtype "time" instead of text. The issue is that you get a lot of functionality served by not mimicing the algorithms already there, just think of the time saved on debugging!

--sd

[skingjack2]

Hello Soren,

Have a few minutes today to work on this further. Attached is what I have for the calculated text field. I looked at your "secondTest" file and it appears to mimic your solution. I will be doing more investigating/testing later this evening to determine why mine is not performing as yours is.

Also, I am planning to perform a calculation of the duration of the flights.

Finally re: text field usage... why not replace with a time field? Good question...answer is I'm not familiar with time functions to implement and unfortunately not aware of the benefits to do it as you suggest.

Jack

[comment]

Why don't you try it this way?

---

There is an old one of Comment's trickeries...

That doesn't look like mine.

---

TimeEntry.fp7.zip

Edited March 28, 2010 by Guest

[Søren Dyhr]

Neither does it look like mine, but it originated from:

http://fmforums.com/forum/showpost.php?post/323374/

--sd

[skingjack2]

Hello,

In the interest of fairness, I'll thank you both (and the unknown author) for locating/creating this file =). I had a quick opportunity to test the file; it appears that when the times are inputted as actual formatted times, the calculations work fine. Unfortunately, the same cannot be said for when military times are inputted (likely because the fields are formatted as times?). Any thoughts on how to fix this?

Jack

[Søren Dyhr]

Which is which, we have uploaded 3 files to this tread and been giving sugggestions to simplifications to what you already have attempted.

Both Michael and I have suggested that you use the virtues of the field formats as time... This is perhaps what gives you problems?

Take a look at the image above - it should work as expected!

--sd

Edited April 3, 2010 by Guest