I am exporting a date field and need to keep the # of characters in the date field.

Scenario: I am parsing a date to three separate fields using the Year(Date) Month(Date) Day(Date) functions(Calc result is text).

Problem: I need to keep the leading zeros in the month and date fields for the export.

Current Result:

Date Field = 2006/01/01

returns

2006 1 1

I need: 2006 01 01, and when exporting into Excel I need to maintain the same # of characters (i.e. 2006 01 01).

Any feedback would be greatly appreciated. I have been hitting this from every angle and can't find the solution.

Thanks in advance...

You need to pad the numbers. Take a look at this thread on padding.

Try:

Right ( "0" & Month ( Date ), 2 )

and same for Day(). That will take care of the leading zeros as far as Filemaker is concerned, but I am not sure Excel will respect that - see here. But Excel understands dates, and can format them, so why such a roundabout way?

"comment", I am trying to automate this export process to provide an output file for another system periodically, ideally I don't want to interact with Excel, currently I am only using Excel as a means of validating my data.

Thanks for the quick responses. "comment", I used your calculation and read your link which helped explain the issue in more detail. It is clearly an issue with how Excel interprets the text file.

Lessons Learned:

When opening my FileMaker exported file as a text file it maintained the leading zeros. However, when opening with Excel it trims the zeros. By changing the import delimiter options in Excel when importing I was able to maintain the zeros.

Thanks again for the help.

Edited November 17, 200619 yr by Guest

Problem: I need to keep the leading zeros in the month and date fields for the export.

Why do you need to do this for Excel? Dates (or any number cell for that matter) in Excel can be formatted to show leading zeros or not, but it's not necessary to actually enter zeros. And if you export date records from FM and then import into Excel, you should get dates without doing anything extra. So I don't guess I understand why you are breaking the date into three pieces and what is happening to them when you get the over into Excel. Can you provide more information?

James

www.james-mc.com

I am using Excel just to view the data.

I have to break the date into into a total of 8 characters (i.e. 1/1/2006 = 20060101) in order for my ASCII file to work with the COBAL system I am importing into.

I have to concatenate several fields into one text string equaling 2050 characters, so having all the spaces and zeros are critical.

Everthing seems to be working good in regards to this issue.

Thanks for all the feedback.

I have to concatenate several fields into one text string equaling 2050 characters, so having all the spaces and zeros are critical.

Then what you're doing makes sense to me now. Glad you have it working.