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 =

Left(Hours;Position(Hours;".";1;1)-1)

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.

Leading and trailing zeros are meaningless in a Number field, and FileMaker is correct to drop them. Use Number functions to process numerical values:

HoursExtract =

Int ( Hours )

MinutesExtract =

Mod ( Hours ; 1 ) * 100