date output format

[saralee]

Hi everyone,

I have a date field in FM Pro defined as the auto-enter field which sets "Date" upon creation. But the output is DD/MM/YYYY which is the same as my PC's system date format. I would like to change it to MM/DD/YYYY instead. Anyone knows how to do so? I couldn't find any options in FM Pro to change the setting/format. Does it have to do with the system setting of my PC?

Thanks a lot! :

[Lee Smith]

Date format is taken from the Operating System.

You will need to create a new field, and use a calculation such as:

Let([

Parts=Substitute(DateField;"/";" ");

Month=LeftWords(Parts;1);

Day=MiddleWords(Parts;2;1);

Year=RightWords(Parts;1)];

Day &"/"& Month &"/"& Year)

This one comes courtesy of Bob Weaver, and posted here

Link

HTH

Lee

p.s. added link

Edited July 12, 2010 by Guest
p.s.

[saralee]

Thanks for your reply, Lee.

I can see your point, but my case is that I only need to change the date format for the future new records, meaning that in the future when I create a new record, the date field will be automatically set to be the current date in the MM/DD/YYYY format. I think that would require the auto-enter setting, right? Can I define some calculation in the calculated-value field under the "auto-enter" tab?

Thanks!

[TheTominator]

Since you have a date field, you can use FileMaker's built-in date functions to do the formatting in a calculation.

Month(theDate) & "/" & Day(theDate) & "/" & Year(theDate)

If you only need to display the date in a different format on a layout (and not export, use in a string calculation, etc), you can specify the precise formatting using the Custom option in the "Date..." formatting.

[TheTominator]

my case is that I only need to change the date format for the future new records, meaning that in the future when I create a new record, the date field will be automatically set to be the current date in the MM/DD/YYYY format.

If the field is defined as a Date, the internal format is an integer number representing a number of days from a specific starting date. Formatting is not stored in the field in the way you are thinking.

If you want to preserve the formatting, you need to make it a Text field with an auto-enter calc.

It is better to embrace a Date field as a FileMaker Date field and work out how to display it (or export it) as you need to where and when you need to.

For example create a second calculated Text field

c_MyDate = ...

formatting the date for your needs if the Layout display options are not sufficient.

Edited July 12, 2010 by Guest

[comment]

in the future when I create a new record, the date field will be automatically set to be the current date in the MM/DD/YYYY format.

Will you also change your OS settings?

There is a BIG difference between displaying a date in this format or another on one hand, and changing the date format used by the file. The latter affects how you need to enter dates and also the result of GetAsText (date) calculations.

[comment]

Formatting is not stored in the field in the way you are thinking.

I don't think that's accurate: you can format the field to display "As entered" - so clearly, the format IS being stored.

[saralee]

Thanks for the advise!! I was able to create another calc date field whose display format meets my needs. My question now is if I can enter any data in the calc date field. It seems that it is not editable as being a calc field.

My final product looks like this - a drop-down calendar date field which displays date in MM-DD-YYYY format.

Thanks a lot!

[saralee]

Thanks, comment!

I can change the setting in OS, but would the users of the database also need to change theirs? I don't know how to change that either~ :

[comment]

First, have a look at the options under File > File Options… > Text > Data Entry. The "file's saved settings" are the ones inherited from the OS when the file was created.

You can change the file's setting by changing your OS settings (while the file is closed), then saving a copy of the file as clone (no records). This is the best route to follow if you develop on a system with different settings than the final user.

[saralee]

Thanks, Comment!

Sorry I keep editing my post. Now I am able to change the OS setting and use "Always use current system settings" option to display the date format I wanted in the client side. But once I publish it in the web, the date setting still stays in the old format. I don't know why it is not changing it in the web. Please advice.

I have another issue to tackle here regarding the web publishing:

after I publish the database via Server Advanced and test it in the web interface, an important function is lost- in my client side, I designed a button to trigger a pop-up window displaying related records, but in the web interface after the publishing, the button does not work. It simply changes the current window to display some other records (sometimes not even related as the way I have set it).

Do you have any ideas what is going on? Thanks a lot! :

Edited July 13, 2010 by Guest