Date calculation (no. of days) - help!

[sticks1977]

I have been trying to work out calculations for a date count function - hence why I'm here!

 

The major calculation I need to figure out is a numeric figure that will tell me the turnover time of any job that comes through our department. But only to count the weekdays and exclude weekends - is it even possible?

 

 

This would be using the "Received" field as the 'Start Date' (Received field has the date automatically generating when a new record is created) and the "Proof Out" field as the 'End Date'. Often this can be the same day so I would presume that it would give me a figure of "0"?

 

Please ignore the calculation that is already in the picture above, I was on another forum and they suggested the "Let" command but as I have gone into the program further, 5.5 does not have this function...

 

Any help would be appreciated, once I know the calculation I can apply it to other areas of the database and simply changing the names of the field. The reason that there are two of each date field (shaded in red) is the 'Received' date is in dd/mm/yyyy format and the 'RECEIVED' field is in Mon-Fri daily format.

 

Thanks to those that can take the time to reply - I would really appreciate a response or perhaps another thread that can point me in the right direction - so far I have been thinking non-stop for 2 days and still can't figure it out!

 

Regards, Shaun (sticks1977)

[AHunter3]

The reason that there are two of each date field (shaded in red) is the 'Received' date is in dd/mm/yyyy format and the 'RECEIVED' field is in Mon-Fri daily format.

 

You don't need two fields for that. One field, FORMAT it differently.

 

 

b) In FileMaker 5, you can use the formula in your screen shot without "Let" just by defining a calculation field "Days" of result type "number" defined as End Date - Start Date +1, then use the rest of the formula in a second calculation field named "Art Days".

[sticks1977]

b) In FileMaker 5, you can use the formula in your screen shot without "Let" just by defining a calculation field "Days" of result type "number" defined as End Date - Start Date +1, then use the rest of the formula in a second calculation field named "Art Days".

 

Thanks for your reply AHunter3, so lemme get this right...

 

Calculation 1:

Days =

Proof Out - Received +1

 

Calculation 2:

Art Days =

Div (Days ; 7) * 5 + Mod (Days ; 7) - (DayofWeek (Proof Out +1)

 

I presume FileMaker already knows that DayofWeek will mean Mon - Fri ?

 

Thanks again for your reply - sure hope this works when I head into work tomorrow morning...

 

Regards, Shaun (sticks1977) smiley-smile

[AHunter3]

DayOfWeek returns a number, 1 for Sunday 2 for Monday 3 for Tuesday etc.

 

Since you're subtracting, that's a good thing.

[sticks1977]

Thanks for your reply Hunter,

 

Calculation 1 works without any problems, it will produce a figure of "1" for a job that has been completed on the same day.

 

The problem I am running into now is it will not create a calculation to exclude the weekend days (Saturday and Sunday). As soon as I paste the calculation for 'Calculation 2' into a field named 'Art Days' I get the following message:

 

Either an operator was omitted, this function cannot be found, or "(" was not expected here

 

Once I click OK from that warning message it is highlighting the Div command at the beginning of the calculation.

 

Any more help and advice would be welcome - I feel that the calculation is almost there - just missing something!!

 

Regards, Shaun (sticks1977)

[AHunter3]

Oops, sorry.

 

The Div function was introduced in FileMaker 7 so you can't use it

 

Div function

Format

Div(number;divisor)

Parameters

number - any numeric expression or field containing a numeric expression

divisor - any numeric expression or field containing a numeric expression

Data type returned

number

Description

Returns the next lowest integer value after dividing number by divisor. The Div function is equivalent to Floor(number/divisor).

Examples

Div(2.5;2) returns 1.

Div(-2.5;2) returns -2.

 

 

I'm not the best person to reconstruct that mathematically. In fact just reading that makes my head hurt.

[sticks1977]

Still need some assistance with this calculation, I have figured a few things out myself and so far this is what I have come up with:

 

DayofYear(Proof Out) - DayofYear(Received).

 

It will give me the correct result of the amount of days, but I am wondering how I can exclude Saturday and Sunday (DayofWeek 1 and 7). I have tried to add to the calculation by placing:

 

- DayofWeek (1) - DayofWeek (7) but if I have a proof that was entered on a Wednesday and out on Thursday it gives me a result of -2!!

 

Any more help from those that know more would be appreciated... feel like I am nearly there with this calculation...

 

Regards, Shaun (sticks1977)

[Techphan]

DISCLAIMER: I started using FM v8.0.

 

Perhaps you could make a calculated number field (YourTable::WorkDays using IF or CASE such that if DayNumber = 1 or 7 you get a 0 or "" answer and if DayNumber=2, or 3, or 4, or 5, or 6 then you get a "1" (without the quotes, of course). The in a second WorkDaysTotal field you get the answer.

 

As far as your calculations go, are you trying to calculate weeks out or just days?

 

Then count number of work days. since the order was placed.

[brsamuel]

I did not verify that FM5 supports the functions used, but you could probably adapt one of these for your purposes:

 

http://www.briandunning.com/cf/185

 

or

 

http://www.briandunning.com/cf/179

[FmproUserJLN]

I wanted to say thank you to AHunter3 - your solution works beautifully and flawlessly in our application of this dating issue. Many thanks. I just wish I could understand the formula! I don't quite understand how it works and what Mod does...nor do I understand the dayofweek less than times 2 and what that is doing. Feeling quite dumb right now :-(