Duration before a time

[BlakeHammond]

Hi I am calling on the community help once again. I explain as best as i can

I am trying to build a service app with automated invoicing. The bit I am stuck on is out of hours.

(IN HOURS 08:00 - 18:00) so On-Site Time (09/01/2021 - 13:00:00) Off-Site Time (09/01/2021 -14:00:00) gives me 1-hour duration on site – This is fine this bit works

(Out of hours before 08:00 On-site or after 18:00 Offsite) On-site time(09/01/20201 - 07:00) Off- Time (09/01/2021 - 09:00) gives me 1 hour out of hours + 1 hour in hours – This Works Too

Now The problem

On-Site Time 09/01/2021 - 01:00 Off-Site - 09/01/2021 02:00)

Gives me 7 hours Out of hours as 01:00:00 is 7 hours before 8 am

and -16 hours for 02:00:00 after 18:00.

Now I know the problem is giving me the answer I have asked which is how many hours before 8 am and how many hours after 18:00.

What I was trying to do and can’t seem to think of a workaround is

between 8 am - 6 pm is in hours

Before 8 am and after 6 pm is out of hours

So if we were called out at 7 am and finished at 9 am it would be 1 hour out of hours because it’s 1 hour before 8 am and 1 hour in hours because it’s after 8 am. Now I need to know how many hours before 8 am for invoicing purposes I.e invoice 1 hour at out of hours rate and 1 hour at in hours rate.

I hope I have explained this well enough for help.

Thanks

Blake

[Steve]

UNTESTED!!!

In all of this, [start] and [stop] must be the same day!!!

In-hours start:

max(list([start], (date([start]) + "08:00:00")))

In-hours stop:

min(list([stop], (date([stop]) + "18:00:00")))

In-hours total:

(
  min(list([stop], (date([stop]) + "18:00:00")))
  - max(list([start], (date([start]) + "08:00:00")))
)

Total time:

([stop] - [start])

Off-hours total:

(
  ([stop] - [start])
  - (
    min(list([stop], (date([stop]) + "18:00:00")))
    - max(list([start], (date([start]) + "08:00:00")))
  )
)

[BlakeHammond]

hey @Steve

Thank you very much i will give that a try.

Now feel free to tell me to do one

I was trying to use datetime with duration to get me the elusive if we started at 23.:00 and finished at 01:00 but it throws an error when you try to work out duration from datetime, which i get.
,
but is it even possible to be able to work out duration if it goes past midnight while also keeping the in hours out of hours. i know standard duration between times will do it.

i wondered about somethimg like if start date+1, 00:00:00 + finish time

if today()= start date, start time + 00:00:00,

Start time + fiinish time

i know that isn’t correct but i sure you get the jist

[Steve]

Duration values most certainly can be produced from DateTime values. Please post a screenshot of the error you get.