7 Replies Latest reply on Sep 28, 2010 7:07 AM by michael_alexander@me.com

# Calculation

### Title

Calculation

### Post

Hello, i've a challenge. Maybe someone can help.

I want to have a calculation like this:

Count days (In month (variable like January, February, etc)) for (Day (variable like monday, tuesday, etc)).

Thanks!

• ###### 1. Re: Calculation

You'll need the year, too.

That can be made with a recursive Custom Function, with a looping script, with a repeating calculation field or even with an unstored calculation.

I like the last way.

So you have 2 number fields ( year and month ) and 1 text field ( DayName ), let give them the names: Y , M and DN

Create a calculation field, name it "result", do not insert any calculation inside it and exit.

Now re-open the calculation editor for that field, insert this calc:

Let([
\$i = \$i + 1 ;
D = Date ( M ; \$i ; Y )
];
( DayName ( D ) = DN ) + If ( Month ( D + 1 )  = M  ; result ; Let ( \$i = "" ; "" ) )
)

and make this calculation UNSTORED.

Note that you could see some comments on this type of calculation and an example on: http://www.fmforums.com/forum/showtopic.php?tid/216662

• ###### 2. Re: Calculation

Great! It's working! i don't now how it works but it works ;).

Now i've a another challenge. The results (of a week) must be multiply with number of hours per day.

Like this:

Y M DN Result Hours per day Total

2010 9 Monday 4 1 4

2010 9 Tuesday 4 8 32

2010 9 Wednesday 5 2 10

And then for a whole week in one calculation...

Result: Grand total for the month: 46 (that's the number i need).

How can i do this? Y and M are variables  and DN is always permanent. Hours per day are fixed by client.

Thanks!

• ###### 3. Re: Calculation

I do not understand the structure of your DB.

Is :

2010 9 Monday 4 1 4

one record ?

If so, a simple summary field ( Total of Total ) will do the job on the found set.

• ###### 4. Re: Calculation

The data is of a database Contract and a database Invoice.

Data contract

Hours Monday from [Time] Monday to [Time] Total Monday [Number (Hours per day)]

Hours Tuesday from [Time] Tuesday to [Time] Total Tuesday [Number (Hours per day)]

Etc.......

Data Invoice

Month [Month]

Year [Year]

Calculation: [Number (Hours per day) for Monday (if completed...)] * Mondays in Month in Year + [Number (Hours per day) for Tuesday (if completed...)] * Tuesdays in Month in Year + etc. until Saturday.

The result is total hours according Invoice for that month in that year.

Clear?

• ###### 5. Re: Calculation

Can you help?

• ###### 6. Re: Calculation

Yes, but I need to see your DB, even a clone.

raybaudi(at)libero(dot)it

• ###### 7. Re: Calculation

I've done it!

Thanks for the help!