Help Please with Excel Export

[Rub88]

Hi,

I am attempting my first go around with an excel export in Filemaker 8.5 - I have the jist of it down, but 2 things have me stumped.

1) I am exporting a field that "sometimes" has leading zeros, on the screen the field shows the leading zeros just fine, but when I export to Excel and then open the file the leading zeros have been dropped. Any way around this???

2) My database has two number fields "Intime" and "Outtime" that are formated in military time IE intime might be 5.5 for 5:30am and outtime might be 17.5 for 5:30pm - when I do this export to excel I need the times to be converted to regular times 5:30 nothing else, no am/pm etc.

I have started to work on #2 by using another field set to CALC but I am not sure of the calculation to use... Any thought from you FM MASTERS out there???

Thanks in advance...

~R

[mr_vodka]

For your first question, see this thread

Excel Exporting with leading zeros

For your second question, I was unaware that military time was represented by decimal parts. AFAIK, its a 24 hour clock where 07:30 is 7:30AM and 19:30 is 7:30PM, unless I missed that part in Basic Training... Where does the decimal factor come in? Why is :30 represented by .5 here?

[Rub88]

Yes, you are right, Military is a little different. But, I still need to convert 5.5 into 5:30 and 17.5 to 5:30 any ideas there?

Thanks,

~R

[mr_vodka]

How about a quick calculation with time result of:

Let ( n= number;

Case ( n ≥ 13; (n - 12) * 3600; n * 3600 )

)

[comment]

Or:

3600 * Mod ( YourWeirdTime ; 12 )

[mr_vodka]

How about to convert 0:30 to 12:30...

Let ( n = Mod ( YourWeirdTime ; 12 );

3600 * ( n + ( not Int(n) * 12) )

)

[Rub88]

Thanks for all the help, I figured them both out with your help!

Thanks again!

~R

[Badbert]

If you're exporting a text file, use Excel's text import wizard from the File > Open menu. On step three where you select the data format for each column, select the time column and change it from general to text. What's happing is, Excel is treating the time data as a number. The only way it will keep the leading zeros if the column is designated text.

[Rub88]

Thanks for the offer on the above post, but I don't think you read my original post very closely.

And as you can see just above your post, I posted that I figured both my questions out with the help of the other users.

But again, thanks for the effort.

~R