Help with calculation formula needed

5 years ago
7 November 2018
2 replies
5 views

+11

bimi82
340 replies

Hello All,

I am working on a formula to show relevant currency £ calculations based on selected dates and times.
The Total formula needs to meet the following criteria:

always calculate the remaining minutes in hourly rate
e.g. if you book for 30min then you pay for 1 hr or if booked for 1hr and 45min then you pay for 2 hrs and so on.
when fromDate and untilDate are equal AND the hourly differences between the dates is below 8 THEN calculate the hour difference * hourly rate (note: if the hourly difference is below 1 hr then total is always hourly rate)
when fromDate and untilDate are not-equal THEN calculate the day difference by 8hrs PLUS any remaining hour difference between the times (for example 1 day and 5hr:30min = 1 day * 8 hrs + 6hrs)

Another example shown below the booking is taken place for 1 day, 5hrs and 15minutes.
The caculaculation in this case would be: (1 day * dailyRate) + 6hrs * 3.50 < unless I am being wrong.

Please feel free to through in your opinions and better efficient ways in achieving the outcome.

Maybe an introduction to using the FORM VIARIABLE may be beneficial of which I am new to.

2 replies

B

+11

bimi82
Author
340 replies
5 years ago
9 November 2018

Well, I have had a try but still failing to make it work 100%.
This is what I have setup as Form Variables:

calculateMinutes

// It only shows the minutes within the hour between the dates.
// Example 09/11/2018 10:00 and 09/11/2018 12:15 > shows 15minutes difference.

(dateDiffMinutes(FromDate, UntilDate)-(60*dateDiffHours(FromDate, UntilDate)))

calculateHours
// This is to say when minutes is above 0 then add + 1 to the hours.
// For example for 1hr and 15 minutes the user gets charged for 2hrs, in other words any minute becomes +1hr.

if(calculateMinutes==0,(dateDiffHours(FromDate, UntilDate)-24*dateDiffDays(FromDate, UntilDate)) ,(dateDiffHours(FromDate, UntilDate)-24*dateDiffDays(FromDate, UntilDate))+1)

calculateDays

// Shows the days difference between the days.

dateDiffDays(FromDate,UntilDate)

calculateDailyCost

// Calculates the daily cost based on the amount of days if above 0

// Otherwise it will show the daily cost which will be 0

if(calculateDays>0, calculateDays*lookup("Pool Car","Registration", parseLookup(PoolCar), "Daily Cost"), calculateDays*lookup("Pool Car","Registration", parseLookup(PoolCar), "Daily Cost") )

calculateHourlyRate

// Calculate the hourly rate if calculateHours is between 1 and 8

// Otherwise calculate HoureRate * 8

if(and(calculateHours>=0,calculateHours<=8 ), lookup("Pool Car","Registration", parseLookup(PoolCar), "Hourly Rate")*calculateHours, lookup("Pool Car","Registration", parseLookup(PoolCar), "Hourly Rate")*8 )

TOTAL FORMULA

// This calculates the two figures together.

calculateDailyCost+calculateHourlyRate

The scope seems to be working as following:

- calculate hourly rate
- calculate daily rate if it's more than 8hrs

- cap the daily rate if the hours difference is more than 8 hrs

- summarise the total price

Failing to do:

- calculate the daily cost + hourly cost + the difference hours when it goes above 8hrs
For example, booked it for 2 days and 10 hrs and 30 minutes (from 13/11/2018 07:00 am until 15/11/2018 17:30 pm)

my current calculations: 2 * dailyRate + 8hrs Rate
However, the remaining 2rs + 30min (any minutes becomes an hour so it is 3 hrs) are not taken into account....