Jump to content

Date Calcs


This topic is 4408 days old. Please don't post here. Open a new topic instead.

Recommended Posts

I am trying to create a function that will tell me whether a date criteria has been met given some data inputs.

I just want a basical True/False to be spit out if the criteria is not met, but I'm getting wonky results, especially with date ranges that extend from one month or year to another.

Let ( [



StartDate = "1/3/2010" ; Calc = "<" ; CheckDate = "1/10/2010" ;

Duration = 3 ;

Units = "Days"

] ; 

Case ( 

         Units = "Days" ; If ( Evaluate ( Date ( Month ( StartDate ) ; Day ( StartDate ) + Duration ; Year ( Startdate ) ) & Calc & Date ( Month ( CheckDate ) ; Day ( CheckDate ) ; Year ( CheckDate ) ) ) ; "Days True" ; "Days False" ) ;

         Units = "Months" ; If ( Evaluate ( Date ( Month ( StartDate ) + Duration ; Day ( StartDate ) ; Year ( Startdate ) ) & Calc & Date ( Month ( CheckDate ) ; Day ( CheckDate ) ; Year ( CheckDate ) ) ) ; "Months True" ; "Months False" ) ;

         Units = "Years" ; If ( Evaluate ( Date ( Month ( StartDate ) ; Day ( StartDate ) ; Year ( Startdate ) + Duration ) & Calc & Date ( Month ( CheckDate ) ; Day ( CheckDate ) ; Year ( CheckDate ) ) ) ; "Years True" ; "Years False" )

         ; 

"Error" ) 

& "¶Human Friendly Calculation: CheckDate ( " & CheckDate & " ) must be " & Calc & " " & Duration & " " & Units & " From StartDate ( " & StartDate  & " ) in order to pass validation"  )

I'm still a relative noob at FMP so if anybody can point to where I may be going wrong that would be appreciated.

Link to comment
Share on other sites

You have some issues with data types here. First, "1/3/2010" is text, not a date. This flaw happens to be "cured" by using the Month(), Day() and Year() functions - since these expect a date as the parameter. So:

Month ( "1/3/2010" )



is actually evaluated as:




Month ( GetAsDate ( "1/3/2010" ) )



This will return 1 - IF the date format in use is m/d/y.





The Evaluate() function, OTOH, expects a text parameter. In your example, the supplied text is:



"1/6/2010<1/10/2010"



Again, these are not dates, and the evaluation proceeds as follows:




1/6/2010 < 1/10/2010 



=



.0000829187396352 < .0000497512437811



=



False

Link to comment
Share on other sites

  • 2 weeks later...

I guess I'm a little confused then. Evaluate requires text yet I need to perform these calcs on dates, so is evaluate the right choice for me or is there another option out there that will fit the need better?

is it as easy as adding GetAsDate ( "text" ) to the first portion of the Let statement for the dates I'm passing in?

Link to comment
Share on other sites

is evaluate the right choice for me or is there another option out there that will fit the need better?

Hard to say - you haven't told us what the need is. Can one assume that StartDate, Calc, Duration, Units and CheckDate are fields? If so, what are the choices you are willing to allow in the Calc field? If there are only a few, I would try to eliminate Evaluate() altogether and do a Case() on those choices. Otherwise you could try something like =

Let ( [

endDate = Case ( 

Units = "Days" ; Date ( Month ( StartDate ) ; Day ( StartDate ) + Duration ; Year ( Startdate ) ) ; 

Units = "Months" ; Date ( Month ( StartDate ) + Duration  ; Day ( StartDate ); Year ( Startdate ) ) ;

Units = "Years" ; Date ( Month ( StartDate ) ; Day ( StartDate ); Year ( Startdate ) + Duration ) 

) 

] ;

Evaluate (

"GetAsDate ( " & Quote ( endDate ) & " ) " & Calc & " GetAsDate ( " & Quote ( CheckDate ) & " ) " 

)

)

This should return 1 (True) or 0 (False).

Link to comment
Share on other sites

You might look into these functions; including the Date function:

Date

Purpose: Returns the calendar date for month, day, and year.

Format: Date(month;day;year)

http://fmhelp.filemaker.com/fmphelp_11/en/html/func_ref1.31.12.html#1028277

Link to comment
Share on other sites

Hard to say - you haven't told us what the need is. Can one assume that StartDate, Calc, Duration, Units and CheckDate are fields?

Only Calc, Duration, and Units are user definable fields. Checkdate and Startdate are both based off of a related record but for the sake of creating and checking to make sure that the formula works correctly, I have made them enterable fields.

If so, what are the choices you are willing to allow in the Calc field? If there are only a few, I would try to eliminate Evaluate() altogether and do a Case() on those choices. Otherwise you could try something like =

Let ( [

endDate = Case ( 

Units = "Days" ; Date ( Month ( StartDate ) ; Day ( StartDate ) + Duration ; Year ( Startdate ) ) ; 

Units = "Months" ; Date ( Month ( StartDate ) + Duration  ; Day ( StartDate ); Year ( Startdate ) ) ;

Units = "Years" ; Date ( Month ( StartDate ) ; Day ( StartDate ); Year ( Startdate ) + Duration ) 

) 

] ;

Evaluate (

"GetAsDate ( " & Quote ( endDate ) & " ) " & Calc & " GetAsDate ( " & Quote ( CheckDate ) & " ) " 

)

)

This should return 1 (True) or 0 (False).

I will be limiting the "calc" field to values that can be used in the Filemaker Calc Engine (<,>,=, etc...)

After looking at the above formula I think I know the problem with mine. It looks as if your entire Evaluate string is contained within quotes and mine is not. I've never used the Evaluate function before so I think that's where I was going wrong.

Link to comment
Share on other sites

It looks as if your entire Evaluate string is contained within quotes and mine is not.

Not really. My string to evaluate will come up as:

GetAsDate ( "1/3/2010" ) > GetAsDate ( "1/10/2010" )

I will be limiting the "calc" field to values that can be used in the Filemaker Calc Engine (<,>,=, etc...)

The problem is with the "etc...".

Link to comment
Share on other sites

Not really. My string to evaluate will come up as:

GetAsDate ( "1/3/2010" ) > GetAsDate ( "1/10/2010" )

The problem is with the "etc...".

Hah, I only meant the "etc..." to placehold the other Calc functions (Greater than or equal to, less than or equal to, equals...)

Anywho, I have the calc working as intended now so thank you for the help!

Link to comment
Share on other sites

This topic is 4408 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.