I need to convert a Age calculation to a Grade

[Mark Jones]

I have a need to take a Age Calculation and convert it to a School Grade Calculation.

For Example 5 years old would show 0 for Kindergarten

6 years old would show 1 for First Grade

7 years old would show 2 for Second Grade.

I tried to copy and paste my age calucation into a text field that had a case function tied to it that would convert the numbers to the grade level...but my age calculation doens't have whole numbers.. it shows for example 5.888111119.

I have looked at the round function trying to get it to round up but that doesn't really round the number up it only displays it as rounded.

This is my age calculation

Round( ""; 0 ) + (Get(CurrentDate)- Birthdate) / 365

I sure would appreciate your help here.

I publish not for profit software for Church Boyscouts. (Royal Rangers)

Mark Jones

[RalphL]

You could use a case function, i.e.,

Case (Age = 5; 0;

Age = 6; 1;

...

Age = 17; 12)

Or you could just use Age - 5.

Change you age calc to Round((Get(CurrentDate)- Birthdate) / 365; 0) This is not the best age calc but it will work.

[Ender]

A note of caution: School grades are generally independant of the age of a student. A six year old child could be in Kindergarten or 1st grade. Children are also held back or moved ahead grades on occasion.

[Mark Jones]

You da man! Thanks. I changed my Age calculation and it fixed my Whole number problem. Now it all works great. I do appreciate it very much.

I had already used the calc feature but it didn't work as the numbers were not whole numbers. Then in the Grade field I did the Age -5 calculation this morning and had that working great. But it still didn't give me the whole numbers. So you put me right on what I needed. Thanks BUNCHES!

Mark Jones

[Mark Jones]

Thanks for the note on the age may not line up with the grade. Where this is important I can adjust the grade.

I do appreciat the info!

Mark Jones

[CobaltSky]

Change you age calc to Round((Get(CurrentDate)- Birthdate) / 365; 0) This is not the best age calc but it will work.

It depends what you mean by 'work'. It will produce an accurate result much of the time and be inaccurate the rest. Moreover its inaccuracy will become more pronounced in proportion to the age being measured, so that an elderly person's age would be incremented three weeks or more too soon.

In part the issue arises from the fact that the calc takes no account of leap years. Whilst the inaccuracy could be reduced by using 365.25 in place of 365 in Ralph's expression, it would still be out by a day or two here and there.

However FileMaker provides sophisticated date handling in its calcultation engine, so if you want the calc result to be precise you could simply let FileMaker do the heavy lifting for you. An example of a FileMaker 7 expression formula that will produce correct age results for all cases is:

Let([n = Get(CurrentDate); b = DateOfBirth];

Year(n) - Year( - ((Month(n) + Day(n) / 100) < (Month( + Day( / 100))

)

That will give you the current age (assuming that the calc is unstored), but I suspect that you face another problem in arriving at a *grade* - which is that a student doesn't go up a grade as soon as it's their birthday. At best, I'd assume that the grade is based on some arbitrary date such as 1 January, or perhaps the start of the school year.

That being the case, one approach you might consider would be to create a global field into which you can enter the reference date against which the ages (and therefore grades) will be calculated. To do this and return a grade number in one action, you might use an expression such as:

Let([r = gReferenceDate; b = DateOfBirth];

Year® - Year( - 5 - ((Month® + Day® / 100) < (Month( + Day( / 100))

)

where gReferenceDate is the global date field into which you enter the reference date for the current school year and DateOfBirth is a standard date field holding the student's birth date.

[RalphL]

I don't think that originator was interested in the precision offered by your calcaulation. Since grade is not a true function of age. Many students will have a birthday during the school year but their grade will not change until the next school year. Your second formula takes this into account. There are other factors that could effect the grade level of a student.

[Bekah]

Well I'm interested. but I don't get part of it.

((Month(n) + Day(n) / 100)

So, example, um,

Month (12) + Day(28) = 40 /100?? = .4

I get I think that you're subtracting the number of days for accuracy but it makes no sence. I have profit sharing calcs to get right. I was using 365.25

[CobaltSky]

Hello Bekah,

Glad to hear you're interested.

The part of the calculation:

((Month(n) + Day(n) / 100) < (Month( + Day( / 100))

is a boolean expression designed to resolve to a 1 if the person's birthday has not yet occurred in the current year, or otherwise to a zero.

By dividing the day number by 100 and adding it to the month number, the date is being rendered in the form of a fraction solely for the purposes of comparison. So for instance if the current date is the ninth of July, the expression (Month(n) + Day(n) / 100) will produce 7.9. If the person's date of birth is the eighth of October, then (Month( + Day( / 100) will produce 10.8.

Thus the boolean expression is evaluated as (7.9 < 10.8) which is true so it returns a 1.

This is because if the person's birthday has not yet occurred in the current year, then the result of the preceding expression:

Year(n) - Year(

must be adjusted by a year to correctly reflect their age prior to the anniversary of their birth date.

This is a method which is generally applicable to the accurate calculation of anniversaries and the various applications of anniversarial accumulation (such as age and profit sharing etc).

[Bekah]

I will use this. i think I get it but I'll have to read it a billion times. It makes sense as I read what you say and then when you stop talking it disappears again.

I wonder what tomorrow will bring. I have to tell my boss. I don't know if he'll be happy and several employees displeased. or the other way around. wish I could disappear for a week or so. wish i could see the excel sheets because I used that same calc that was used before before this program. I wonder how much difference it will make. Glad it's not my money.

Oh! Thank you CobaltSky

[Ben Feingold]

I thought I'd try to apply your advice to a different problem but I'm having a little trouble. I'm using the Case function:

Case (test1, result1

[-Queue-]

"XX:YY:ZZ WM" is not a valid time. Use Time( XX, YY, ZZ ) instead.

[HowardCrut]

If you want something like: November 12, 1988 = 10th grade

go ahead an try:

Year(Today)-( Int(((((((NumToText(Year(Today) - DOB year - Case(Today< Date(DOB month, DOB day, Year(Today)), 1, 0))*365.25) + (NumToText(Mod(Month(Today) - DOB month + 12 - Case(Day(Today) < DOB day, 1, 0), 12))*30.435) +NumToText(Day(Today) - DOB day + Case(Day(Today) >= DOB day, 0, Day(Today- Day(Today)) < DOB day, DOB day, Day(Today- Day(Today)))))-2191.5))-((Month(Today)-8) * 30.435+Day(Today)-1)

)/365.25 )+1) -12)

This is with the assumption that the student is six years old by the cut off date of August 1 to be able to enter 1st grade.

At the very end of the formula, 8 = August and the 1=the 1st. So, if the cut off date was the 30th of September(9), then at the very end it would say:

...((Month(Today)-9) * 30.435+Day(Today)-30)

)/365.25 )+1) -12)

clear as mud?