How to find out year of transition

[JulesB]

I am hoping that someone can help me with the below problem.

I am trying to create a field in a database to indicate the date that a trainee will move to thier next year of training. They go to next year of training after 52 weeks training. They may have multiple placements during a particular training year.

I have their training  start date (trainee::startdate... their start and end date (traineeplacement::startdate and traineeplacement::enddate) of each job (in a linked table), their percentage of full time (traineeplacement::PercentageFullTime)  and the calculated number of weeks whole time equilavent in the job (traineeplacement::durationweeks).

Through using the cumulative time in a training (traineeplacement::training-duration), in excel I can find the end of the block before the one they would have spend 52 weeks using Max statment with an imbedded if statement to find max value less than 52 weeks in cumulative time in training. I cannot seem to do this in filemaker. I would then like to find the next line in the traineeplacement table and from that I can calculate the end of training year date from start of placment date, % of fulltime, and number of weeks neediing to be worked to get to 52 weeks...

I hope this makes sense.

[Ocean West]

Welcome Jules

would you have an example spread sheet you could post to further explain the data set?

[JulesB]

testSheets.xlsx

Attached is a test of spreadsheet.. I don't have the actual info for trainees, as this is something that I will transfer into this format form the 2-3 spreadsheets it is in at present