Kind of complicated situation...I have an Excel file that has a column with a number that corresponds to a time duration (e.g. 2.40 indicates 2 hours 40 minutes).
I am importing that column into "Hours", a Number field in FMP. I then have 2 fields that are used to calculate the actual number of minutes represented by the number in "Hours":
Hours Extract =
Minutes Calc =
(hours extract * 60 ) + minutes extract
My problem is that when I import, trailing zeros are stripped so that 2.40 shows up in FMP as 2.4.
2.40 calculates as 160 minutes but 2.4 erroneously calculates as 124 minutes. If I set the formatting of "Hours" to display 2 decimals, 2.4 *displays* as 2.40 but when I click in the cell it is really 2.4 and that's what's used in the formula.
Any suggestions on how to preserve the trailing zero?
P.S. I can't control how the time field is displayed in Excel because that's a constraint of the data source for that report.