Jump to content
Sign in to follow this  
Rub88

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

Thanks in advance...

~R

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
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 this post


Link to post
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 this post


Link to post
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 this post


Link to post
Share on other sites

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

Thanks again! ;)

~R

Share this post


Link to post
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 this post


Link to post
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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.