Change of Date

[The Missing Man]

I have a field with the following date in it.

Month/Day/Year

I then want to take the Month and Day and add in a different year. (01/25/2000)

The problem is when i

Select; Left Date_As_Number; 5) I get Day/Month as opposed to Month and Day (25/01)

Does anyone have any thoughts why?

Thanks in advance,

.

[Raybaudi]

What is the result of this calculation ?

Month ( YourDateField )

[LaRetta]

It is important to use FileMaker's date functions instead of text functions so no matter your settings, it will use the correct values.

Month ( yourDate )

Day ( yourDate )

Year ( yourDate )

... but it sounds like your data is under different date format (DD/MM/YYYY) than what you want (MM/DD/YYYY). Can you confirm how your actual data is formatted when you click into the field? It appears to be old, imported dates if the actual data includes the unnecessary 0. Users rarely include the 0 if not needed and many programs format with the leading zeros (such as calculations).

You may need to create an empty clone (which would inherit your current US settings) then import your data into it. Perform a search for ? in this date. Do you get any?

[comment]

I have a field with the following date in it.

Month/Day/Year

This is pretty much useless, unless we know:

• Is it a Date field or a Text field?

• What is the date format of your OS and of your file?

The problem is when i

Select; Left Date_As_Number; 5) I get Day/Month as opposed to Month and Day (25/01)

Here too, we don't know what Date_As_Number is in your file. BTW, Left Date_As_Number; 5) is not a valid expression. And if Left ( YourField ; 5 ) returns "25/01" then the field does NOT contain a Number.

[The Missing Man]

I will try and explain better:

I have a field [Months::Date A] with the following date in it (01/25/1998, which would read as Jan 25,1998), based on the following format. Month/Day/Year

I then want to create a new field [Months::NewDate] by taking the Month and Day component out and adding it in combination with a different year [Months::Global Year Change] to read as. (01/25/2000 or what would be Jan 25, 2000)

The problem is when i do the following script it gets all messed up:

Go to Field [Months::NewDate]

Insert Calculated Result [select; Left (Months::Date A; 5) &"/" & Months::Global Year Change]

I get Day/Month= 25/01 (as opposed to Month and Day (01/25)

Since there is no such thing as a month 25 everything is screwed up.

Does this explain it better?

Thanks in advance,

[The Missing Man]

Date=Date

Date A = convert Date into number

New Date =number

GlobalYearChange=Number

Basically I converted date into numbers to make my changes. Can I do everything and leave it constantly as a date?

I will try and explain better:

I have a field [Months::Date A] with the following date in it (01/25/1998, which would read as Jan 25,1998), based on the following format. Month/Day/Year

I then want to create a new field [Months::NewDate] by taking the Month and Day component out and adding it in combination with a different year [Months::Global Year Change] to read as. (01/25/2000 or what would be Jan 25, 2000)

The problem is when i do the following script it gets all messed up:

Go to Field [Months::NewDate]

Insert Calculated Result [select; Left (Months::Date A; 5) &"/" & Months::Global Year Change]

I get Day/Month= 25/01 (as opposed to Month and Day (01/25)

Since there is no such thing as a month 25 everything is screwed up.

Does this explain it better?

Thanks in advance,

[comment]

Does this explain it better?

No. Why don't you post a simple file showing the problem.

[The Missing Man]

Ok here is the file with notes that hopefully explain things better

DateIssueJunk2.fp7.zip

[LaRetta]

Account name? Password? :-)

[The Missing Man]

adksmk for both sorry about that

[LaRetta]

Your dates are US format. You have Date_As_Number set to Date instead of number. I believe that the Date_As_Text break (showing dd/mm/yyyy) is a bug reported about text dates.

But why are you manipulating the dates into number and text? Dates are best worked with as dates. And, instead of multiple Months table occurrences, you can use one. I would re-think your approach in general. :-)

[comment]

Would something like this work for you (it's hard to figure what are you really after)?

DateIssue1.zip

Your dates are US format.

What leads you to this conclusion?

[LaRetta]

I was making that determination (incorrectly it seems) based upon changing the date field to Edit and 'As Entered'. I forgot to check the file settings.

I was wrong. It was set to use current settings. If changed to use file settings, it is international. My apology for the error. :crazy2:

[comment]

I believe "international" these days would be YYYY-MM-DD (as per ISO 8601).

---

Please excuse my constant nitpicking.

Edited December 28, 2011 by comment

[LaRetta]

Ah, nitpick is fine, Michael, it pushes for accuracy and precision and I always appreciate that. It is sometimes the subtle misunderstandings (or knowledge-holes) that can bite us the most.

Then how would you describe the difference between day first versus month first? Does not Europe use d/m/yyyy? But we cannot refer to it as European format because Australia uses day first as well. In fact, it seems it is only us yankees which put the month first. I think we are still in prehistoric days.

I would have sworn I have read top Developers calling this 'European format' International as well, over the years, but I could probably not find a post to confirm it. I want to be correct in my terminology and understanding so thank you for bringing it up.

corrected it to display short date d/m/yyyy

actually, more than US uses it ... Federated States of Micronesia, Belize, Philippines and Palau also.

[comment]

I have noticed that the word "international" is often used as an antonym to "domestic". I prefer to spell it out as d/m/y.

[The Missing Man]

Thanks for your help, much appreciated by all. It looks like I can make this work.