Automatic date calculation

[Sera]

[color:blue]Quick explanation: I'm looking for an auto calculation on record creation for the next available check run day.

Now... a bit longer explanation of my conundrum:

I get an invoice and I process it through my FMP7 relational database before putting it into the financial system (yay calculations and copy/paste).

Checks (aka payments) are run on Fridays (unless there is a holiday) and the deadline for entry into the system is the Wednesday 9 days prior (also unless there is a holiday). The Wednesday deadline is specifically noon, but I can work around that if I can't figure how to add that into the calc.

Example: For a check run on Friday, November 10 then the invoice has to be entered into the financial system by noon on Wednesday, November 1.

Why calculate only on record creation? Because it's easier to search my database for my answers than the financial system and I need the payment date to remain the unchanged from the initial payment date unless I change it manually. (ie I need to search for all invoices I paid on 11/03/06...)

Here are some little extras:

1) Due to holidays not all check runs land on Friday (ie Thanksgiving week, checks are run on Wednesday) and not all entry deadlines land on Wednesday (any time there is a Friday holiday, then my deadline is moved to Tuesday). If there is a way to work this in, then yay!

If not, then I can work around with a manual change in the payment date. The number of Friday holidays are small (3 of them in 2006... of course Thanksgiving causes extra issues because it's Thursday and Friday rather than just Friday), so I'm willing to leave this out if need be.

2) I'd like to be able to override or update manually (or through a button/script) the next available payment day -- ie I create the record on 11/1/06 and it calculates payment on 11/10/06 (or 11/09/06 since the 10th is a holiday for us), but find that I can't pay the invoice until 11/17/06 so I will have to manually change the payment date.

3) At some point I have to change the date from 11/1/06 to 110106 because our financial system doesn't like the slashes. I don't want to do it in this calculation, cuz I would like to display it on the form I have to add to the invoice with the slashes present.

[color:purple]OK... so basic best case scenario:

At 11:59 am on Wednesday, Nov 1, I create a record that calculates the payment date as 11/09/06 OR 110906 (cuz the 10th is a holiday for us).

Then at 12:01 pm on Wednesday, Nov 1, I create a record that calculates the payment date as 11/17/06 OR 111706.

Additionally, any records that I create between 12:01 pm on Wednesday, Nov 1 and 11:59 am Wednesday, Nov 8 will calculate the payment date as 11/17/06 or 111706.

Edited November 3, 2006 by Guest

[comment]

Here's a quick formula that will give you the Friday of next week, or the Friday after that if submitted on or after Wednesday noon:

InDate - DayOfWeek ( InDate ) + Case ( DayOfWeek ( InDate ) = 4 and InTime ≥ 43200 or DayOfWeek ( InDate ) > 4 , 20 , 13 )

Holidays are more tricky, and it's difficult to advise without knowing WHICH holidays. US federal holidays can all be computed - though it will be quite a formula in version 6. You might want to have a table of holidays, and if the result matches a holiday record, adjust accordingly.

The formula returns a DATE. I'd advise against changing it into a TEXT string like "110906". This can easily be done in another calculation field, with a text result.

[Sera]

WoW! Thanks for the quick response!

This particular problem is in a FMP7 database.

The holidays are of a small number and are federal holidays as I work for a county government. I had thought about using a table for the holidays, which may be the best option and not too difficult to set up.

You suggested:

InDate - DayOfWeek ( InDate ) + Case ( DayOfWeek ( InDate ) = 4 and InTime ≥ 43200 or DayOfWeek ( InDate ) > 4 , 20 , 13 )

What is InDate? and InTime?

Edited November 6, 2006 by Guest

[comment]

Why was I sure this was for v.6?

InDate and InTime are the date and time when the paperwork has been submitted. If you prefer to use a timestamp for this, use:

GetAsDate ( Submitted ) - DayOfWeek ( Submitted ) + Case ( DayOfWeek ( Submitted ) = 4 and Hour ( Submitted ) ≥ 12 or DayOfWeek ( Submitted ) > 4 ; 20 ; 13 )

[Sera]

My profile insisted on saying that I was using version 6 (which I used to, but haven't in some time... my bad for not updating my profile).

Anyway -- thank you so much for your help!

This works great!!! I am currently using the time-stamp option and it works like I need it to.