AnsweredAssumed Answered

Trailing zeros lost on Excel import

Question asked by rubinjm on Feb 7, 2019
Latest reply on Feb 7, 2019 by rubinjm

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.

Outcomes