5 Replies Latest reply on Jan 12, 2011 9:09 AM by philmodjunk

# Calculation-> date doesn't work.

### Title

Calculation-> date doesn't work.

### Post

Hey,

I have a personal code in one field, which contains date of birth information: year, month, and day. And I have another field: date of birth. I tried to add "Auto-enter->Calculated value" to date of birth field, so whenever i specify personal code, it would automatically enter date of birth. For no particular reason, it just doesn't work and all i see in browse mode after creating new record and entering personal code is question mark. Strangely enough, when I view data in data viewer, it returns just the way it should be. Maybe it's because DOB field is set to "DATE" type? But I want it to be set to date, because i want to use drop down calendar other times when personal code is not available.

Here's a formula i put in calculation:

Let ( [
data = Sales::Persicode; // field i am using
Y1 = Middle (data;1;1); // first character of persicode and it determines the century (1-2: 18XX; 3-4: 19XX; 5-6: 20XX)
y = Case (Y1 > 4; "20"; Case (Y1 >2; "19"; Case (Y1 >0; "18"))) & Middle (data;2;2); // year calculation
m = Middle (data;4;2); // month
d = Middle (data;6;2); // day
] ;
y & "-" & m & "-" & d
)

• ###### 1. Re: Calculation-> date doesn't work.

The calculation is entering data in a format FileMaker can't convert into a date and you then get the ?

First problem:

y has three values possible: "20", "19", or "18". Where in your calculation do you compute the rest of the year to get a value for the year that is 4 digits such as 1961?

Once you correct y to compute the entire year, use the date function as your final expression:

Date ( m ; d ; y )

• ###### 2. Re: Calculation-> date doesn't work.

as you see:

y = Case (Y1 > 4; "20"; Case (Y1 >2; "19"; Case (Y1 >0; "18"))) & Middle (data;2;2)

so, first Case.... is getting "20"; "19"; or "18"

and other part: & Middle (data;2;2)

so, basically it's 19 & 61 = 1961

• ###### 3. Re: Calculation-> date doesn't work.

Yep missed that, it's just that the text:

The text: "1961-08-08" isn't recognized as a date by FileMaker. Date fields in FileMaker are actually number fields with special formatting to change the number of days since December 31, Year 0000 into a date format. You can actually add and subtract dates in FileMaker as though they are numbers and this can be very useful.

Using the Date function should get things working for you.

• ###### 4. Re: Calculation-> date doesn't work.

it did. but it returned me with the message in calculation that i cannot use list function with LET, so i had to remove all variables and use actual fields and calculations.

• ###### 5. Re: Calculation-> date doesn't work.

You should have been able to use the Let function. I don't see any "list" function in your example, which makes that message a bit odd. You probably had either a ] or ; missing or in the wrong place.