Determine year's holidays (CALC)

[QuinTech]

Bob Weaver had posted a sample file a while ago with a script that determines holidays for a selected year. I was interested in a calc field, so i put one together. (Sorry if this has already been posted, but i couldn't find it.)

The sample file determines the following US holidays:

New Year's Day (Jan. 1)

Martin Luther King's Birthday (3rd Monday in January)

Washington's Birthday (3rd Monday in February)

Memorial Day (last Monday in May)

Independence Day (July 4)

Labor Day (1st Monday in September)

Columbus Day (2nd Monday in October)

Veteran's Day (November 11)

Thanksgiving Day (4th Thursday in November)

Christmas Day (December 25)

The solution adjusts for:

1. the fact that some of these holidays fall on a weekend and adjusts accordingly, assigning the holiday to Friday or Monday as appropriate;

2. cases where Jan 1 falls on a Saturday, thus making December 31 of the previous year a holiday; and

3. the fact that many of these dates were different prior to 1972.

It also allows a user to selectively assign declared holidays, as in the case of June 11, 2004 being declared a federal holiday in honor of Ronald Reagan's funeral.

I combined this with Mikhail Edoshin's Smart Ranges to create a calculation that returns the number of official business days between two dates.

Filemaker version 6, i imagine it wouldn't be hard to convert it to version 7.

Jerry

busDay.zip

[QuinTech]

Attached is the companion piece to the above file. No need to download the old one any longer, it is included in this attachment.

To use, open the file Dates.fp5 (if you're converting to v7, open busDay.fp5 first so your relationships won't be broken). Enter a start date and an end date to compute the number of business days between them. (Note that the number of days is inclusive of the start and end dates.)

To see what the business days are, look at the file busDay. You will see a checkbox there that allows you to mark a particular day as a holiday.

Please let me stress that i did not come up with the calculation in the field Dates::z_dateRange on my own. I stole it straight from Mikhail Edoshin. My business days example is but a small addition to his original, useful, and thoroughly amazing idea. Read it if you dare.

J

BusinessDayCalculatorv6.zip

[LA Girl]

Hi, I saw this and thought I could use it as a platform to do a list of date calculations. I need a list of all the business dates in the year (minus the weekends and holidays - which I need to choose because we don't always go by the federal holiday list, although having both would be fine in the check boxes). My final list though, needs to have this list of dates and then two columns - one that calculates 12 business days from the date (minus weekends and my holidays that I check) and one that calculates 15 business days from the date. Any ideas?

[QuinTech]

Here's a way to do it scriptologically. Select your custom holidays and run the one script.

Each time you change your customized holidays, you'll have to re-run the script. I can't think of a way (for now) to do this through a calculation.

Also, since you indicated that you don't necessarily consider all federal holidays as holidays for your purposes, i changed the file to allow you to override the default setting in either direction. If you select the OVERRIDE checkbox, the date will become a business day if not already marked so, or become NOT a business day if it was marked so.

J

busDay2.zip

[LA Girl]

Wow, I'm just now getting to this. Thanks so much for the script, but it doesn't work quite right. For example, the date in column one is 1/4/05 and for 12 days it says 1/23/05 but it should say 1/21/05 (it's not supposed to count the weekends and Holidays (MLK was 1/17). Oh, and I changed the script to enter find mode first so I can search only for this year. Less time time to perform it that way too. Thank you!! Sorry it's taken me a bit to get back. The beginning of the year was hectic but now I have some time to play aroung with this.

[QuinTech]

Change the script step:

If["zsf_DaysAway = 12"]

to

If["zsf_DaysAway = 12 and IsEmpty( zsf_TwelveDays)"]

.

HTH,

J

[LA Girl]

Hmmm, I changed what you suggested but it's still counting holidays.

[LA Girl]

Oh, and the loop doesn't stop.

[QuinTech]

Really? I don't get either of those issues. 1/4/05 plus 12 days shows 1/21/05, and the loop does stop.

Try the newest attachment.

busDay.050303.zip