Ok so I was just fooling around with calculating the time and I got a strange result. I used the following formula to calculate the elapsed time:
((DateEnd - DateStart) * 86400) + TimeEnd - TimeStart
The elapsed time field shows "1e+12" and "999999767942" when I click into the field. I am guessing I need a calculation to convert the number to hours and minutes. So I searched 1e+12 and found some stuff in the knowledge base about Excel compatibility which brings more questions... Is a timestamp in filemaker compatible with Excel timestamps? Does Excel even have a timestamp function - Is a timestamp a universal thing like for example? I entered the number into Epoch Converter and it gave me a date back in 2011 so I am guessing this was't epoch time
You may want to look at the following functions to help parse the info
along with the Left, LeftWords, Middle, and MiddleWords and of course Substitute functions. If that's the exact way the string comes in, specifically noting the space between the date and time, then it should be a breeze, one calculation for each category. Your strange time issue might just be a simple solution as converting the text with GetAsTimestamp and subtracting the two.
In FileMaker , subtracting one time value from another gives you elapsed time in seconds. The same result is produced via the difference between two timestamp values, but now you get an accurate result even when midnight falls between start and stop.
Also, you can select either time or timestamp result types when your result is elapsed time in seconds.
I jumped in on trying some things and didn't get very far. I am going to take a look at the kb for each function and see if it sheds some light. Any suggestions on basic calculation tutorials I might work through?
I assume that means that a former version of FMP didn't and these are feature additions, would that be correct? By the way... You are helping everywhere in the forums. Are you a moderator?
"The information is separated with spaces"
Are you sure that those are spases? Could they be tabs?
Could you post a real log ?
The features that I describe have been around for a long time. They are not new.
A few years back, I was recruited to be the first (and so far only) volunteer "community leader" of this forum.
Hi Ray, yes I am pretty sure since I took it into TextWrangler using "display invisibles". Unless you know something I am not aware of.
Phil, I left Filemaker back at around V 3.x and so I have a lot of new ground to cover. Thanks for the time you put in. I am sure many appreciate it.
MsWord can also be used to show invisible characters. (Click the "Pilcrow" button).
Yes you are right but I find TextWrangler has a lot less overhead and bloat so at launch it opens quicker and generally feels much more responsive. I do use it quite a bit as my default though. I use TextWrangler when I want to be sure I am not getting any MS Control Codes secretly inserted.
I gave Steve the "game point" on this one since it was the closest to what I needed. I elected to do some research on the topic and found some interesting stuff on time.
With regard to Excel:Excel stores dates as sequential serial numbers so that they can be used in calculations. By default, January 1, 1900, is serial number 1, and January 1, 2008, is serial number 39448 because it is 39,448 days after January 1, 1900.
----More and more I have come to believe that it's better not to export things to Excel from Filemaker as the results can sometimes turn into a frustrating experience. My most recent "surprise" was exporting a repeating field and gaining a 'full understanding' of what it means to lose subsequent values in a repeating field.