Time Off database Problem

[Pescador]

I have created a database to track employee time off. I use a date field for both fields, start date and end date. I have set it up to just subtract the two to get the difference. It just hit me that if someone takes time off that spans the weekend then it will count Saturday and Sunday as days off. I have been trying to figure out how to take care of the problem. Does anyone have a solution for the problem. Thanks for your help.

[bcooney]

I see that you are using 8.5A. I would cruise through Brian Dunning's Custom Functions website, and see if you can find a CF that calcs just week days between two dates. Kurt Otto's WeekDaysDiff() function looks like it might be what you need.

WeekDaysDiff Custom Function

[Orlando]

Hi Pescador

I have made a few tweaks to a recursive Custom Function I put together for finding the date x number of working days away from a specified date.

Here it is.

xCF_CountDays ( startdate ; enddate ; zero )

Let ( [ 



Date = startdate ;



DayInitial = Left ( DayName ( date ) ; 1 ) ; // Get the first letter of the Day Name, and late check if its a "S"



BankHolidayCheck = FilterValues ( date ; // Filter this list down so only have a date if there is a match between the Date and Bankholidays

"// All England Bank Holidays for 2007 - 2009, ADD AND REMOVE LINES ASS APPROPRIATE, you could also use the list function and have the dates in a seperate table.

¶01/01/2007¶06/04/2007¶09/04/2007¶07/05/2007¶28/05/2007¶27/08/2007¶25/12/2007¶26/12/2007¶

¶01/01/2008¶21/03/2008¶24/03/2008¶05/05/2008¶26/05/2008¶25/08/2008¶25/12/2008¶26/12/2008¶

¶01/01/2009¶10/04/2009¶13/04/2009¶04/05/2009¶25/05/2009¶31/08/2009¶25/12/2009¶28/12/2009¶

" ) ; // End of Bank holiday list



BankAlert = If ( not IsEmpty ( BankHolidayCheck ) ; 1 ; 0 ) ;



CountSkipDay = If ( DayInitial = "S" or BankAlert = 1 ; 0 ; 1 )





] ;



// Start of Calculation



If ( date ≥ enddate ; 



zero - If ( CountSkipDay = 1 ; 0 ; 1 ) ; 



xCF_CountDays ( date + 1 ; enddate ; zero + CountSkipDay )





) // End of If Function





) // End of Let function

This will also check and omit any bank holidays; I have set this for England bank holidays, but can be changed quite easily.

Let me know if this works for you. or if you need any more explanation on how it works.

Regards

Orlando

[Fitch]

And at least one answer is here:

How Can I Calculate The Total Days From a Starting Date To End Date Excluding Saturday And Sunday?

You might also want to try the FileMaker Knowledge Base and search for "weekend."

[Pescador]

How could I have it check to see if the holiday falls within the date range that they are requesting time off. For example, an employee wants to take July 2nd thru the 5th off. The 4th of July is a holiday so the only days that would count against their leave is the 2nd, 3rd and 5th. Is there a way to check this. I hope this makes sense.

[Orlando]

Hi Pescador

The custom function above will do that, if we are on the same lines.

If you input 02/07/2007 as the start date and 05/07/2007 as the end date the result will be 3. It will also exclude any public holidays if you modify the list accordingly.

One thing I forgot to mention is the parameter "zero" needs to be replaced with "0".

If you would like I will post a demo file for you.

Orlando

[Pescador]

If posting the file is not a problem I would appreciate that. How would it look if it was only checking for holidays and not including them in the total? So not worrying about weekends.

[Orlando]

Here is the tester file I used.

Working Day Checker Example

Hope this does the trick.

[Pescador]

I found this on FileMaker.com forum. I found it interesting and am trying to get it to work correctly. I have been working on it but have not got it to work properly. I am pasting the text below. Will someone with more understanding interpret it and make a sample of it. Thanks in advance.

If a holiday (of one or more days) occurs between StartDate and EndDate, and you don't want to count the holiday as a work day, then some changes are required. First, create a new Table, HOLIDAYS, which contains the following fields:

Date (Date)

Counter (Number)

Holiday (Text) - Optional

Use the following table to enter the values:

Date Counter Holiday

1/1/07 1 New Year's Day

1/19/07 2 Martin Luther King

2/16/07 3 President's Day

5/31/07 4 Memorial Day

7/5/07 5 Independence Day Observed

9/6/07 6 Labor Day

10/11/07 7 Columbus Day

11/25/07 8 Thanksgiving

12/25/07 9 Christmas

1/1/08 10 New Year's Day

In the Relationships Graph, create a relationship between StartDate in the DATES table and Date in the HOLIDAYS table. Create another table occurrence for the DATES table (DATES 2) and the HOLIDAYS table (HOLIDAYS 2) and create a relationship between EndDate in the DATES 2 table and Date in the HOLIDAYS 2 table.

Create the following fields in your main table:

Lookup Low (Number, lookup)

Lookup starting from the DATES table from related table "HOLIDAYS", copy value from field "::Counter"

If no exact match, copy next lower value

Lookup High(Number, lookup)

Lookup starting from the DATES 2 table from related table "HOLIDAYS 2", copy value from field "::Counter"

If no exact match, copy next lower value

These two fields determine the number of holidays that have occurred between StartDate and EndDate. For example, StartDate = 6/15/07 and EndDate = 7/15/07. 6/15/07 looks up into HOLIDAYS, and no match is found. However, the next lower value is 5/31/07 (Memorial Day), and 4 is returned from Counter. 7/15/07 looks up into HOLIDAYS 2, and no match is found. However, the next lower value is 7/5/07 (Independence Day Observed), and 5 is returned from Counter. Subtracting Lookup Low from Lookup High (5-4=1) returns the number of Holidays that have occurred between 6/15/07 and 7/15/07.

[Orlando]

Hi Pescador

I have had a look at this and recreated it as discribed and dont think this will work for you, unless I miss understand what you are trying to achive.

What this solution does is simply count the number of holidays, or records in the holiday table, that fall between the start date and end date. There is a slight floor in this that does not count the holiday if it is the same as your start date.

This will not however count weekends, unless you create a record in Hoidays for every Saturday and Sunday.

My understanding is you want to count the number of working days between a start date and an end date, if this is the case have another look at the demo file I posted as this will do the trick, This will exclude any weekends and holidays, as set by you, and give you the number of working days between the start date and end date.

Let me know if I am going down the wrong path.

Regards

Orlando