Problem with date/time based calculations.
I do consulting work for ambulance services and as part of that work I do a LOT of time based calculations. Since swapping to a Mac about 3 yrs ago I've been doing nearly all of this in Excel with pivot tables and formulas. This has worked, but is cumbersome so I am looking for a good database solution. I am currently using the 30 day trial version of Filemaker Pro 11 and am generally impressed but I'm having a problem with some time-based calculations that will be a deal breaker if I can't find a solution.
Each day I get a flat-file export (.xls format) from a clients dispatch software and in that file are 9 fields that contain a date and time in this format:
These are the fields that I must be able to make calculations from. I spoke with FileMaker yesterday because I was having trouble getting these to work correctly and they suggested formatting the fields as text fields. I did this and it appeared to work fine. FileMaker performed a calculation and it came back formated exactly like i wanted. Unfortunately the calculation was wrong. Really wrong. I am attaching a screen shot of what I'm getting.
I am hoping that I can get this resolved pretty quickly so I can really get a good sense of how FileMaker will work for me before the trial period ends.