3 Replies Latest reply on May 20, 2011 8:51 AM by raybaudi

# Calculate price based on duration

### Title

Calculate price based on duration

### Post

Hello,

I am currently working on a database to store information about amount of time a staff member works, amount owed and amount paid in. My transactions are created automatically. The first stage is I am trying to work out how much is owed based ont he duration of time worked. The transactions are created automatically and the duration field is created in hh:mm:ss format and is already rounded to nearest minute.

The amount of time worked is based on time band groupings. Below is what I would essentially like to achieve:

less or equal to 36 minutes ; price 1
37 to 52 minutes ; price 2
52 - 66 minutes ; price 3...... etc

I have looked at using the Case function however how can I get it to work properly based on the format I have (due to volume of records it would be impractical to change the field values manually)

Kind regards

• ###### 1. Re: Calculate price based on duration

Create a calculation field with calc:

Case(
duration <= 36 ; price1 ;
duration <= 52 ; price2 ;
duration <= 66 ; price3 ;
...
)

• ###### 2. Re: Calculate price based on duration

Hmmm, since the duration field is: "created in hh:mm:ss format and is already rounded to nearest minute"

It's possible that Raybaudi's calculation may not work. What data type is this field? Time, Text, or Number?

• ###### 3. Re: Calculate price based on duration

Yes, Phil is right.

If the duration field is a Time field:

Let(
m = Div ( duration ; 60 ) ;
Case(
m <= 36 ; price1 ;
m <= 52 ; price2 ;
m <= 66 ; price3 ;
...
)
)