# Help Please with Excel Export

## Recommended Posts

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???

~R

##### Share on other sites

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?

##### Share on other sites

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

##### Share on other sites

How about a quick calculation with time result of:

Let ( n= number;

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

)

##### Share on other sites

Or:

3600 * Mod ( YourWeirdTime ; 12 )

##### Share on other sites

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

Let ( n = Mod ( YourWeirdTime ; 12 );

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

)

##### Share on other sites

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

Thanks again!

~R

##### Share on other sites

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.

##### Share on other sites

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

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×

• ### Who Viewed the Topic

1 member has viewed this topic:
cymainformatica
×
×
• Create New...