Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Date Calculation


tomr40
 Share

Recommended Posts

Hello,

I'm using FMP 6.0 in a Driver Education Program for several reports including class lists, attendance lists, financial status lists, and etc. I also use it to print completion cards for each student. These are used by the student to either get his Learner's Permit from the state (blue card) or to indicate completion of behind the wheel instruction which is needed to get a driver's license (white card). The blue card has fields for student's name, birthdate, school location, instructor's name, and date issued. Date issued is where I'm having the problem. If the student if fifteen the "date issued field" needs to be the date of his last class, but if the student is fourteen it needs to be his birthday in the year when he is fifteen.

 

The database has the following fields: Birthdate-a date field, Session-a text field, Date of Last Class-a calculation field that uses an If then calculation based on what Session he is in. Ex If( Session= "CDH 11-12 Sep" , "10/03/2011" , If( Session= "CDH 11-12 Oct", "11/04/2011", If(Session="CDH 11-12 Nov","12/09/11",If( Session= "CDH 11-12 Jan", "01/23/2012",If( Session= "CDH 11-12 Mar", "03/23/2012",If( Session= "CDH 11-12 Apr", "04/23/2012","06/22/2012")))))). This works fine. It has an Age field that uses the following to calculate age: Year(Status(CurrentDate)) - Year(Birthdate) - If(Status(CurrentDate)= 15 , Date of Last Class, Birthdate + (365*15)+3 ). It works, but it's Mickey Mouse and has to be manually adjusted for leap years. Is ther a better way to calculate Date of last Class?

Link to comment
Share on other sites

I am not positive, but are you looking for a better solution to: Birthdate + (365*15)+3 ? If so, I think: Date ( Month (Birthday), Day (Birthday), Year (BirthDay) + 15) should fit the bill.

Link to comment
Share on other sites

Is ther a better way to calculate Date of last Class?

 

First let's bring that calc field up to FileMaker 3.0 standards: you don't have to use IF in calculations now that we're no longer using FileMaker 2 ;) !

 

If ( Session= "CDH 11-12 Sep" , "10/03/2011" , If( Session= "CDH 11-12 Oct", "11/04/2011", If(Session="CDH 11-12 Nov","12/09/11",If( Session= "CDH 11-12 Jan", "01/23/2012",If( Session= "CDH 11-12 Mar", "03/23/2012",If( Session= "CDH 11-12 Apr", "04/23/2012","06/22/2012"))))))

 

becomes

 

Case ( Session= "CDH 11-12 Sep" , "10/03/2011" , Session= "CDH 11-12 Oct", "11/04/2011", Session="CDH 11-12 Nov","12/09/11",Session= "CDH 11-12 Jan", "01/23/2012", Session= "CDH 11-12 Mar", "03/23/2012", Session= "CDH 11-12 Apr", "04/23/2012","06/22/2012")

 

Next, your resulting text string should be explicitly converted to a date:

 

TextToDate (Case ( Session= "CDH 11-12 Sep" , "10/03/2011" , Session= "CDH 11-12 Oct", "11/04/2011", Session="CDH 11-12 Nov", "12/09/11",Session= "CDH 11-12 Jan", "01/23/2012", Session= "CDH 11-12 Mar", "03/23/2012", Session= "CDH 11-12 Apr", "04/23/2012","06/22/2012"))

 

(and at the bottom make the calculation field result type be "date")

 

Now let's get rid of the hardwired stuff and turn it into a formula that's flexible and will work for any year (or, for that matter, century) instead of being hardwired to 2011-2012. Step A:

 

Let's simplify what we're looking for in the Session strings — we care about months, notthing else:

 

 

TextToDate (Case ( PatternCount (Session, "Sep")>0 , "10/03/2011" , PatternCount (Session, "Oct")>0, "11/04/2011", PatternCount (Session, "Nov")>0, "12/09/11", PatternCount (Session, "Jan")>0, "01/23/2012", PatternCount (Session, "Mar")>0, "03/23/2012", PatternCount (Session, "Apr")>0, "04/23/2012","06/22/2012"))

 

 

Now to test for whether a given year is a leap year: In FileMaker 6, I'd use a separate field, IsLeapYear, calculated from a Year field. Unless you are deleting the previous year's data and starting over each year, you should have a Year field for each course record, containing a number such as 2011 or 2012 or 2013 or whatever. Heck, even if you DO delete your data annually for some reason, create a date field. You want it stored, as part of the records, not abstracted from Status(CurrentDate), because an unstored calculation field will be less useful here.

 

IsLeapYear = calculation field, result type number, Case (IsValid (GetAsDate ("2/29/" & YearField)), 1, 0)

 

 

With that field in place we go back and universalize our previous formula:

 

TextToDate (Case ( PatternCount (Session, "Sep")>0 , "10/" & (3 + IsLeapYear) & "/" & YearField , PatternCount (Session, "Oct")>0, "11/" & (4 + IsLeapYear & "/" & YearField, PatternCount (Session, "Nov")>0, "12/" & (9 + IsLeapYear) & "/" & YearField, PatternCount (Session, "Jan")>0, "1/23/" & YearField, PatternCount (Session, "Mar")>0, "3/" & (23 + IsLeapYear) & "/" & YearField, PatternCount (Session, "Apr")>0, "4/" & (23 + IsLeapYear & YearField ,"6/" & (22+ IsLeapYear) & "/" & YearField"))

 

 

Additional Edit: Under some circumstances the date of last class may fall into the year AFTER the "yearField"; you'd know that better than I would. If, for example, the class that ends in January tends to start in December, and you use a yearField value representing the year the student STARTED the class, you'd want to add 1 to YearField for the "Jan" sessions.

Link to comment
Share on other sites

I am not positive, but are you looking for a better solution to: Birthdate + (365*15)+3 ? If so, I think: Date ( Month (Birthday), Day (Birthday), Year (BirthDay) + 15) should fit the bill.

 

 

Dreygo-Not sure where the reply goes so I'll put it here. Your suggestion works perfectly! Thanks.

Link to comment
Share on other sites

AHunter3,

 

As a retired teacher myself, I appreciate the way you handled my problem. You gave me step by step instructions that I have been able to understand and incorporate into my database. There were some other calculation fields besides "Date of Last Class" that had IF statements in them. Using your instructions I was able to change them all over to CASE.

 

I'm hung up now in getting rid of the "hardwired" stuff, as you put it. I got this change entered:

 

TextToDate (Case ( Session= "CDH 11-12 Sep" , "10/03/2011" , Session= "CDH 11-12 Oct", "11/04/2011", Session="CDH 11-12 Nov", "12/09/11",Session= "CDH 11-12 Jan", "01/23/2012", Session= "CDH 11-12 Mar", "03/23/2012", Session= "CDH 11-12 Apr", "04/23/2012","06/22/2012"))

 

In the IsLeapYear calculation field when I enter Case (IsValid (GetAsDate ("2/29/" & YearField)), 1, 0) I hang up with a message that has the GetAsDate function highlighted and it says, "Either an operator was omitted, this function cannot be found, or "(" was not expected here" I've got a screenprint of it, but I'm not sure I'll be able to attach it.

 

Could it be that FileMaker 6 does not have a GetAsDate function? I couldn't find it in either of my references.

 

If you feel challenged about the problem or dealing with me, please go on, otherwise let it go. I'm totally satisfied. I'm way better off than I was before, the database is working, and I learned a lot. Thank you for your effort and Happy Thanksgiving

Link to comment
Share on other sites

In the IsLeapYear calculation field when I enter Case (IsValid (GetAsDate ("2/29/" & YearField)), 1, 0) I hang up with a message that has the GetAsDate function highlighted and it says, "Either an operator was omitted, this function cannot be found, or "(" was not expected here" I've got a screenprint of it, but I'm not sure I'll be able to attach it.

 

Could it be that FileMaker 6 does not have a GetAsDate function? I couldn't find it in either of my references.

 

 

Damn... sorry, sorry, sorry! I thought I had carefully combed through my post for new-version terminologies that were not in use in FileMaker 6 but I missed that one!

 

 

Make that...

 

 

Case (IsValid (TextToDate ("2/29/" & YearField)), 1, 0)

Link to comment
Share on other sites

 Share



×
×
  • Create New...

Important Information

Terms of Use