Calculate Price based on duration and extract business hour and out of business hours.

Hello Everyone,

I’m looking for some advices on how to or help to code from you.

Let me introduce my request, i have an app in Zoho creator i use to track my interventions and make invoicing at the end of the month it’s a app I made myself and I’m not at all very strong in coding or formulas. It’s a very basic app. I’ve tried to find some helps from Chat GPT but unfortunately I think I’m so newbie that it didn’t worked 😂

I’m looking to automate a bit some calculations to help me gain way of time when invoicing end of month. Actually let’s say my intervention is sold in a package of 4h at 100€ for the thoses 4 first hours.

Passed 4h of work then i apply an extra hourly rate for each hour more worked for an amount of 10€ per hour during business hours and 15€ out of business hours.

I’m looking to create a deluge script or formula to extract from the duration all extra hours more than the 4 first hours already included in my package separately for Business hours (let’s say 08:00-17:00) and hours worked Out of business hours (all hours outside the first window) rounding them to the hour more (all started hour is counted as an hour for example 01:15 should be counted as 02:00) and then if possible automatically calculate the total price in front of it for the extra hours.

I have for that total 4 fields date time :

1 for date time depart from home

1 for date time arrived on site

1 for date time leaving site

1 for date time arrived back home

i’m actually getting durations in 2 formula fields, 1 for total time from home to home and 1 from arrived on site and leaving site with the help of a formula I found down here:

(((input.Arrived_Home - input.Start_Travel)) / (1000 * 3600)).toLong() + " Hours " + (((((input.Arrived_Home - input.Start_Travel)) / (1000 * 3600)).toDecimal() - (((input.Arrived_Home - input.Start_Travel)) / (1000 * 3600)).toLong()) * 60).toLong() + " Mins"

from this field we are able to calculate the duration of the travel and time passed on site. But I don’t know how can I get a price calculated automatically from that.

Will really appreciate some helps if solution is easy for someone.

Thanks on advance and Take cares !!