9 Replies Latest reply on Jan 28, 2013 10:08 AM by TomForbes

Date Calculation Using Case Function and Greater Than Not Working

Title

Date Calculation Using Case Function and Greater Than Not Working

Post

I need to calulate the mileage costs based on various rates within ranges of dates.

For example, if the date of travel falls between July 1, 2012 and December 31, 2012 the travel cost value = .555, and if the travel date falls between January 1, 2013 and June 30, 2013 the travel cost value = .565

I have tried the following case function for the field <<mileage_cost>> with 2 records that contain the dates "12/12/2012" and 1/31/2013" respectively. The function returns the value ".555" for both cases, but should return ".565" for the second case.

Case(
travel_date > 6/30/2012; .555;
travel_date > 12/31/2012; .565
)

• 1. Re: Date Calculation Using Case Function and Greater Than Not Working

let check if the date field is defined as date and not text

• 2. Re: Date Calculation Using Case Function and Greater Than Not Working

Yes, the field is defined as date format. And the case function seems to work for the first condition, but not the second one.

• 3. Re: Date Calculation Using Case Function and Greater Than Not Working

this is the correct function

Case(
travel_date > 6/30/2012 and  travel_date < 1/1/2013; .555;
travel_date > 12/31/2012; .565
)

In your formula the first test is always satisfied

• 4. Re: Date Calculation Using Case Function and Greater Than Not Working

I inserted the case syntax as you suggested:

Case(
travel_date > 6/30/2012 and  travel_date < 1/1/2013; .555;
travel_date > 12/31/2012; .565
)

But the function now returns the value ".565" for both records: one record that contain the date "12/12/2012" and the other record contains "1/31/2013".

• 5. Re: Date Calculation Using Case Function and Greater Than Not Working

try using the date function. may be FM does not convert date correctly

Case(
travel_date > date(6;30;2012) and  travel_date < date(1;1;2013); .555;
travel_date > date(12;31;2012); .565
)

• 6. Re: Date Calculation Using Case Function and Greater Than Not Working

That was it! Awesome. Thank you

• 7. Re: Date Calculation Using Case Function and Greater Than Not Working

You should avoid hard coding the year(s). You could do this by setting up a global field where the year would be entered and then use a local variable (set as the content of the global field) in your calculation.

• 8. Re: Date Calculation Using Case Function and Greater Than Not Working

A note of explanation: The / symbol is the division operator. So you when you type:

6/30/2012

FileMaker evaluates that as 6 divided by 30 divided by 2012.

Date, or GetAsDate ( "6/30/2012" )

Can be used to use a specific date in a calculation, but as Rick as commented, there are likely better ways to handle these different dates than 'hard coding' them into a calculation like this. You may find that looking values up from a related table of records is much easier to maintain over time.

• 9. Re: Date Calculation Using Case Function and Greater Than Not Working

Rick and Phil,