Guidance needed for date calculations!

[Mike G]

I could sure use some of you guys expertise here. I know this is possible but just don't have the skills or knowledge yet to pull it off. Trying to set this up for my daughter's daycare. Here is the situation... Several of the children have daycare paid by a government program and we need an accurate way to track billable days. Payment is made on a FullTime or PartTime basis. Non School Age children are always FullTime. SchoolAge children are PartTime on school days and FullTime on days they are out of school or Holdiays. Each child has a RATE depending on their classification, i.e. Toddler, PreSchool, PreK, SchoolAge FullTime, SchoolAge PartTime. Absences are paid for so they don't apply. Most billing periods will have either 10 or 11 days because this is submitted on a Half Month billing period. Week ends are not counted. Ultimately I am wanting to enter a Billing Start Date and Billing End Date and have returned to me the number of days for each child. The agency gives us a StartDate when they qualify and and EndDate when they no longer qualify...i.e... 9/1/2004..9/30/2005. The EndDate is subject to change if they lose qualifications for some reason.

Already have a database with all kids enrolled with all the basic info. Kids on the government program we'll call NCI and there is a field in the dB called NCI with a yes or no checkbox allowing me to filter out those who qualify. There is also a field for StartDate and EndDate (ex. 9/01/04...09/30/05) to indicate their qualification period. I know I need some sort of 'global' field for a StartBillingDate and EndBillingDate...(ex. 12/1/04...12/15/04. Also, I have a dB for Holidays and dates they are not in school where they will be in the daycare all day...if they are of school age. Each record is a date of the holiday like Thanksgiving holidays or Christmas holidays and any other date they are not in school.

What I need to do is calculate the number of billable weekdays for each child.

School Age children could have two figures... something like 9 PartTime days and 2 FullTime days if the dates in the billable period contained dates in the Holidays dB. Non School Age children would simply be a number which was the number of weekdays between the billable StartDate and EndDate. What seems complicated to me is having the BillableEndDate compare to the Qualifying EndDate to insure we don't bill for days they don't qualify. Example would be if the billing period was 12/1/04...12/15/04 and the agency told us a chld's last qualifying day was 12/13/04 then instead of charging for 11 days we could only charge for 9 days.

I know it's possible to calculate the difference between dates and return a number of weekdays only...although I don't know how. And, I sure don't know how to do the date comparison thing.

Any Ideas Out There?

[Barbecue]

Here's a simple looping script to calculate number of weekdays between two dates.

# Using global fields datestart, dateend, idate, and n

Set Field [ idate ; datestart ] 

Set Field [ n ; 0 ] 

Loop Exit Loop If [ idate > dateend ] 

  If [ DayOfWeek( idate ) > 1 and DayOfWeek( idate) < 7 ] 

     Set Field [ bench7::n ; bench7::n+1 ] 

  End If 

  Set Field [ bench7:: idate ; bench7::idate + 1 ] 

End Loop 

If you have FileMaker Developer, you could create a custom function to handle this calculation

[Barbecue]

For the qualifying versus billing date comparison, you could create a calculated field returning a date:

EndDateActual = If( EndDateBilling > EndDateQualifying;

EndDateQualifying;

EndDateBilling

)

This will return either the last qualifying date or the last billing date, whichever comes first. Would that do what you need, or am I misunderstanding?

[Mike G]

I think that would work.

Here's an example of what I meant.

If the billing cycle is from 12/1/04 to 12/15/04 then I'm wanting a calculation to tell me there are 11 billable week days. Should the qualifying date be less than the End Billing date...say 12/13/04 then there would only be 9 billable week days.

[Mike G]

Forgive me because I'm confused.

First... what is idate?

Second...what is bench7?

I already have a field QualifyingStartDate and QualifyingEndDate as well as BillableStartDate and BillableEndDate. Where would idate fit into this? I'm very new at figuring out calculations.

[-Queue-]

You don't necessarily need a script. You could use a calculation like

Case( BillableStartDate <= QualifyingEndDate;

Let([ DateBeg = Max( BillableStartDate; QualifyingStartDate ); DateEnd = Min( BillableEndDate; QualifyingEndDate ); DW1 = DayOfWeek(DateBeg); DW2 = DayOfWeek(DateEnd) ];

Case( not (Position( DW1 & DW2; "1"; 0; 1 ) or Position( DW1 & DW2; "7"; 0; 1 )); 1 + DateEnd - DateBeg - 2 * Div( DateEnd - DateBeg ; 7 ) - (WeekOfYear(DateBeg) <> WeekOfYear(DateEnd) and DW1 > DW2) * 2; "error" ) ); 0 )

For holidays, you could create two calculations to determine the min and max valid dates,

cBegDate := Max( BillableStartDate; QualifyingStartDate )

cEndDate := Min( BillableEndDate; QualifyingEndDate )

and then create a relationship based on:

cBegDate <= DateHoliday

AND

cEndDate >= DateHoliday

then use Count(HolidayRel::serial) to determine the number of holidays included in the date range.

[Barbecue]

Forgive me because I'm confused.

First... what is idate?

idate is just the name I chose for a field to use as a loop variable. It could have been called X, or LoopCounter, or something like that.

Second...what is bench7?

Bench7 is just the name of the table I was using in when I created the script. With FM 7, field references include the table name. So if you have a table called Bob, and a field called Fred, the field will be listed as Bob::Fred in your scripts.

I already have a field QualifyingStartDate and QualifyingEndDate as well as BillableStartDate and BillableEndDate. Where would idate fit into this? I'm very new at figuring out calculations.

I basically posted two separate things, one a way to count weekdays between two dates, and the other a way to determine the correct date range. I probably should have put them up in the opposite order though, because before you can figure the number of weekdays, you need to figure out what the actual starting and ending dates are.

Using your own field names, ...

ActualEndDate = If( BillingEndDate > QualifyingEndDate; QualifyingEndDate; BillingEndDate)

Queue's calculation does the same thing in a different way, using the min() function instead, a function I tend not to use mostly because I forget it's there. (Fortunately, there's generally about four or five ways to do anything in FileMaker.) Both methods produce the same result. In plain English:

If the QualifyingEndDate is later than BillingEndDate, use the BillingEndDate. Otherwise, use the QualifyingEndDate.

You didn't specifically mention it, but I assume (and so did Queue) that the QualifyingStartDate could also be later than the BillingStartDate.

Once you've determined what the actual start and end dates are, you can then add up the number of weekdays by using the DayOfWeek() function. This function gives you a number from 1 to 7 that indicates the day of the week for a given Date. 1 is Sunday, 2 is Monday, etc., so results of 2 through 6 indicate weekdays.

My script works by starting from the ActualStartDate and going through to the ActualEndDate, checking each date to see if it's a weekday. If it is, increase the count of weekdays by one. Otherwise don't do anything.

Queue's calculation apparently works by some sort of voodo incantation or something, I'm still trying to figure it out

Queue's calculation is cool because it doesn't require running a script to compute the answer, just enter the date values and the answer pops up. My script is probably easier to understand by reading it, but you need to run the script to get the answer.

If your field names match what he used, you can copy and paste his text into a calculation field, and it will just work.

[Mike G]

First, you are correct. It is a possibility that the QualifyingStartDate could be later than the BillingStartDate if a child started after the beginning of the billable period. Glad you noticed that because I didn't mention it.

Second...wow... you guys have give me a lot to absorb but I do thank you. Sure nice trying to learn and having this kind of knowledge available and willing to assist. Very Grateful Indeed....

Thank You So Much

Mike

[-Queue-]

Let me translate the voodoo incantation into semi-plain English.

The crux of the calc is

1 + DateEnd - BillableStartDate - 2 * Div( DateEnd - BillableStartDate; 7 ) - (WeekOfYear(BillableStartDate) <> WeekOfYear(DateEnd) and DW1 > DW2) * 2

1 + DateEnd - BillableStartDate merely gives the difference between the two dates, inclusively. 2 * Div( DateEnd - BillableStartDate; 7 ) gives the number of weekend days between the two dates, based on the number of full weeks contained in the difference, e.g. if there are 22 days difference, then this portion returns 6 weekend days. And finally, (WeekOfYear(BillableStartDate) <> WeekOfYear(DateEnd) and DW1 > DW2) * 2 gives an extra two days, if the dates are not in the same week and the first date is later in the week than the second, because the 2 * Div( DateEnd - BillableStartDate; 7 ) will not take this partial week into account.

The full calc is then the Number of inclusive dates - number of weekends based on full 7 day weeks, times two - additional 2 days if the dates are in different weeks and the first date is later in the week than the second.

The remainder of the calc's body merely tests whether either date is a Sunday or Saturday and returns 'error' if one or both is true.

[Mike G]

I'm lost already...

Am attaching two files so maybe you can show me the error of my ways if you have time. I didn't make a relationship because wasn't sure what to do.

Also, Not quite sure about the EndDate field shown in the calc. I made a field called ActualDate and put the calc you listed earlier in the post in it. I know I've done something wrong. Can you let me know what...please?

workdays.zip

[-Queue-]

DateEnd isn't a field; it's a variable defined in the calculation that is equivalent to your cEndDate. Change your ActualDate to be a number and rename it BillablePTDays. Then re-enter one of the dates and you should see 11 in the field, for 11 billable PT days.

For the Holidays, go to File -> Define -> File References and create a new File Reference for your Holidays file. Then go into your Relationships graph and click the first button on the bottom left to define a new TO. Change the File to 'Holidays' and click 'OK'. Click the second button on the bottom left to create a new relationship. Select Workday Test for the left-hand table and cBegDate as its field. Then select Holidays and dHoliday for the right-hand table, and select <= as the join operator in the middle of the dialog. Click 'Add'. Now select Workday Test::cEndDate as the left side and leave Holidays::dHoliday selected on the right side. Change the join operator to >= and click 'Add'. Click 'OK' and you now have a multi-criteria relationship to Holidays. Create a calculation of Count(Holidays::dHoliday) and deselect the 'Do not evaluate if all fields are referenced' option so that you will receive a zero result if there are no holidays within the date range.

[Barbecue]

Queue, I don't understand something in your calculation. Why do we want to display an error or a zero depending on which day of the week the range starts or ends on?

Mike G, I noticed in your file that you're using standard fields configured with auto-enter calculations as the default values, rather than actual calculated fields. I suspect this is not what you want to do. Changing the field to a calculation will mean the value will automatically change when one of the date fields changes, sort of like a formula in Excel.

[-Queue-]

The first Case test should have been BillableStartDate <= QualifyingEndDate, not BillableStartDate <= QualifyingStartDate. Thanks for the catch, Barb. I changed it in the calc above. If BillableStartDate is greater than the QualifyingEndDate, the result should be zero.

I also added a variable for the DateBeg, which I somehow overlooked while trying to work and create the calc at the same time.

The error result should still be accurate. If either DateBeg or DateEnd falls on a weekend, then I think this is a problem that should be investigated since Mike seemed to imply only weekdays would be included.

Auto-enter calcs will update just fine, as long as 'Do not replace existing value for field' is deselected. So this can either be a calc or an auto-enter calc.

[Barbecue]

If either DateBeg or DateEnd falls on a weekend, then I think this is a problem that should be investigated since Mike seemed to imply only weekdays would be included.

Aha, now I understand it! I just assumed he didn't want to count weekends in the total, so I was confused by that part.

Auto-enter calcs will update just fine, as long as 'Do not replace existing value for field' is deselected.

True, but you'd still have the possibility of someone editing the field contents, and even if you lock the field for editing, there's always importing and script errors to screw things up.