Date field help

[dmontano]

I would like to create three fields in order to culminate into an actual date.

Month field (text field)

Day Field (number or text field)

Year Field (number or text field)

Once all three values are in place, I would get a resulting composite date, like: January, 8, 2008 in a forth Date format field.

Most all of my other date fields are based on a Date type field which requires all three (Month, Day, and Year) in one swoop. This is fine, but I have run across a scenario where I wish to be explicit in the information I need entered into the field.

I have tried a few ways to get the data of those three fields back into a "Date" type field, but can not get it to work. Maybe I am brain dead, but I am trying to do this:

Provide a sure fire way of having a User enter someone's birth date.

I want the user to have explicitly clear fields in which enter a date (Since this will be used internationally). So in this one case, I want to actually provide via text drop downs (not calendar drop down) for each of three fields: Month, Day, and Year.

I thought I could just calculate the concatenation of the three text fields with the slash in between the three fields to simulate what Filemaker would typically see in a "date" type field and place that result in a date result - date calculated field - but that gave odd results.

I can not use the drop down calendar because the user would have a time-consuming task of performing many clicks on the calendar to get to the actual date of someones birthday. If I use one field (which would be normal and typical) I run into problems with Users who are familiar with their Date format based on their region.

Can anyone help me solve this?

[Ocean West]

The calc would be:

Let( [

m = MonthField;

d = DayField;

y = YearField

];

GetAsDate ( m &"/"& d & "/" & y )

)

or

Date ( monthfield ; dayfield ; yearfield)

Edited October 12, 2008 by Guest

[dmontano]

Thanks Stephen for the quick reply!

This is what I had before you posted:

GetAsDate ( people_birth_month & "/" & people_birth_day & "/" & people_birth_year )

I changed the calc as you posted and I am still getting weird results. I have attached 2 screenshots to show what I did, and what I am getting in return.

I can not figure it out. I have tried changing the field types to numbers for day and year - but no better. However, if I change the month from a text field value of January to 1 it works. So I guess the answer partly lies in being explicit to filemaker as to the number of the month.

How can I change month of January into 1 in the calc? Part of the confusion I am trying to avoid is the numbers for the User - I prefer to show the Month name. I know that a list of 1 through 12 should imply it is months - but I anticipate someone accidentally recording 8 for the month or September - for example. Any ideas?

Do I extend the Let and define the months into numbers?

[dmontano]

Screenshot of calc used as you presented...

[dmontano]

This calc works as posted here... but I am wondering if there is a "native" filemaker function that returns the same result for the month name into a month number? If anyone knows - I would appreciate the info.

[comment]

Filemaker knows how to output a monthname - but it doesn't understand it as input. If you want to translate a monthname into the corresponding number WITHOUT hardcoding the names, you could use something like:

Sum (

textMonth = MonthName ( 1 ) ;

2 * ( textMonth = MonthName ( 32 ) ) ; 

3 * ( textMonth = MonthName ( 60 ) ) ; 

4 * ( textMonth = MonthName ( 91 ) ) ; 

5 * ( textMonth = MonthName ( 121 ) ) ; 

6 * ( textMonth = MonthName ( 152 ) ) ; 

7 * ( textMonth = MonthName ( 182 ) ) ; 

8 * ( textMonth = MonthName ( 213 ) ) ; 

9 * ( textMonth = MonthName ( 244 ) ) ; 

10 * ( textMonth = MonthName ( 274 ) ) ; 

11 * ( textMonth = MonthName ( 305 ) ) ; 

12 * ( textMonth = MonthName ( 335 ) )

)

This should work as long as textMonth matches a valid monthname in the LOCAL date format. A result of 0 means no match and can be used for error trapping.

[LaRetta]

Now ... talk about a 'native FM function' ... who would have thought that MonthName (335) could return December? I had to see for myself; I thought then that the number is the 'number day-of-year'. It is very cool and it works; even producing December when I enter 365.

But it CAN'T be day-of-year because Help says MonthName ( date ). Knowing that dates are the number of days from 1/1/0001, I tried 733327 and it produced October. Is it because the numbers you entered are translated as year 0001 that it works? So it isn't the day-of-year but actually days of the year but rather DATES in year 0001!!! How in the world did you translate that principle?

I'm not explaining it very well.

Only you would have thought of it, Michael. ROCKIN' :laugh2:

Edited October 12, 2008 by Guest
Corrected year

[comment]

I thought then that the number is the 'number day-of-year'

In a way, it is. GetAsDate ( 335 ) returns Dec. 12, 0001, and both GetAsNumber ( Date ( 12 ; 1 ; 1 ) ) and DayOfYear ( Date ( 12 ; 1 ; 1 ) ) return 335. That's because we are using the first year in FM calendar.

A more verbose formula could be:

Sum (

textMonth = MonthName ( Date ( 1 ; 1 ; 1 ) ) ;

2 * ( textMonth = MonthName ( Date ( 2 ; 1 ; 1 ) ) ) ;

3 * ( textMonth = MonthName ( Date ( 3 ; 1 ; 1 ) ) ) ;

...

)

I just hardcoded the pre-computed numerical values.

[dmontano]

Thanks Comment,

I wrestle with these calculations - sometimes for hours so your input is greatly appreciated. Now I am stuck on this "other" related one... I need to determine "number of days until a persons next birthday" I am using this calc:

people_birthday - Get ( CurrentDate )

It returns the correct number of days ONLY if the person's birthday is in the future of the current date. If their birthday has already occurred for the "current year", then the calculation is placing a negative value in front of the number. I can see why it is behaving as such (I am using "Current Year" in the calc and so it must be a negative value).

So, last night I was messing around with this logic until I just gave up:

If (

people_birthday > Get ( CurrentDate ); people_birthday - Get ( CurrentDate ); people_birthday - Get ( CurrentDate )+1)

I know it is wrong and incomplete but I just threw in the towel and once again decided to "tap-out" and ask for help here.

For clarification:

"people_birthday" field is a calc field defined as:

Date ( Month (people_birth_date) ; Day ( people_birth_date) ; Year ( Get ( CurrentDate )) )

This calculation field appears to be working fine and is being used in the calculation field in question that is displaying the negative value.

[comment]

How about:

Let ( [

today = Get (CurrentDate) ;

thisBD = Date ( Month ( DOB ) ; Day ( DOB ) ; Year ( today ) ) ;

nextBD = Date ( Month ( DOB ) ; Day ( DOB ) ; Year ( today ) + 1 )

] ;

Case ( today < thisBD ; thisBD ; nextBD ) - today

)

---

I used DOB instead of your people_birth_date.

[dmontano]

Thanks Comment!

Works perfect.

[dmontano]

I am getting a "?" mark in the display of some of these "date" calculation fields... if the field(s) that the calculation is dependent upon are empty.

Since these are optional data entry fields, many will be blank and the "?" displaying is an eye sore.

How do I fix this?

[Genx]

Let ( [

today = Get (CurrentDate) ;

thisBD = Date ( Month ( DOB ) ; Day ( DOB ) ; Year ( today ) ) ;

nextBD = Date ( Month ( DOB ) ; Day ( DOB ) ; Year ( today ) + 1 )

] ;

If( not IsEmpty(DOB) ; Case ( today < thisBD ; thisBD ; nextBD ) - today )

)

[comment]

There is only one field that the calculation depends on, the DOB field - so if you leave the "Do not evaluate if all referenced fields are empty" option checked, it should work without modifications to the formula.

If that doesn't help, then check your data for invalid dates.

---

P.S. Unrelated, but I don't see that this has been mentioned before: the calculation must be unstored.

[dmontano]

Genx - thank you very much. I thought it was going to be some sort of checking routine. I appreciate your help.

Hi Comment - you were right. All I had to do was simply check that box. Funny, every time I saw that box I thought "Of course I want to evaluate with or without values - what's the big deal?" Now I see why you WOULD turn it on.

Thanks for the help!