Work hour

[McPolois33]

Hello i have a question that I, by myself can't solve.

Work hours log app

I am trying to figure out a kind of summary page for my app, which will sum SelectedDay, or SelectedWeek

Scenario one (which i got to work but it is not optimal) :

Report multiple entries for each and every CategoryRef(work,work ot1, work ot2, etc). Where each category defines like this:

Work 007:00:00 - 016:00:00

work  OT1 016:00:00 - 020:00:00

work OT2 020:00:00-007:00:00

Drive day 008:00:00-016:00:00

Drive Night 016:00:00-008:00:00

Though I Need to keep track and split reports if time is past certain ranges. Eg: driving from 006:00 to 009:00, then I need to split into 2 reports of 006:00 to 008:00(categoryRef=driveNight) and 008:00 to 009:00 (categoryref=drive day)

This part I have managed to do if I report  everything this way and set the CategoryRef to the correct entry. Though there is a lot of time spending in divide intersecting entries.

The sum page looks kinda good, it works but trying to figure out something smoother

Scenario 2 ideal option

Report With only two categoryref (categoryRef : work , drive )

Automatic calc what is each sub category by time settings/time interval. Defined in Categories sheet like above with time ranges.

Eg when I make a drive 006:00 to 009:00, the sum page calculate that it is 2 h drive night and 1 h drive day)

I have been trying to crack this nut for some time now and I can't figure out how i should do.

some parts of the LogWorkHour sheet and ListCategories

KeyCategory	Categories	Image	tStart	tEnd
17e8e095	Work Day		007:00:00	016:00:00
653a5f17	Work OT1		016:00:00	020:00:00
1307a059	Work OT2		020:00:00	007:00:00
21bfe532	Drive Nigth		008:00:00	016:00:00
1d400b08	Drive Day		016:00:00	008:00:00

Key	CompanyRef	CategoryRef	Datum	Start Time	End Time	Total Time	Note
4ad9b9eb	f0886ad0	Drive Night	21/02/2023	21/02/2023 06:45:00	21/02/2023 08:00:00	01:15:00
a79249d8	f0886ad0	Work Day	21/02/2023	21/02/2023 08:00:00	21/02/2023 16:00:00	08.00
17acb1ee	f0886ad0	Work OT1	21/02/2023	21/02/2023 16:00:00	21/02/2023 16:30:00	00.30
c9910605	f0886ad0	Drive Night	21/02/2023	21/02/2023 16:30:00	21/02/2023 17:10:00	00:40:00

[Marc_Dillon]

I can't make heads or tails of this. Can you more concisely state what is your main question or issue?

[McPolois33]

How The expression should be like.  *scenario 2

I cant figure out how to get it to work

What I am asking for is practically how I should make a expression/expressions to do this:

Within a day reported, these entries are available in WorkHourLog:

Drive	21/02/2023	21/02/2023 06:45:00	21/02/2023 08:15:00	01:30:00
Work	21/02/2023	21/02/2023 08:15:00	21/02/2023 16:30:00	08.15
Drive	21/02/2023	21/02/2023 16:30:00	21/02/2023 17:10:00	00:40:00

Then on the summary page the expressions I am looking for, is something that will do this:

Take the entries from WorkHourLog:

Calculate how much time is Drive,Drive night, work, Work OT1, etc from the entries using the CategoryRef(CategoryRef is ListCategories) time span as reference (as in main post ListCategories). The result should look something like this Like this: 

Drive Night	21/02/2023	21/02/2023 06:45:00	21/02/2023 08:00:00	01:15:00
Drive Day	21/02/2023	21/02/2023 08:00:00	21/02/2023 08:15:00	00:15:00
Work Day	21/02/2023	21/02/2023 08:15:00	21/02/2023 16:00:00	07:45
Work OT1	21/02/2023	21/02/2023 16:00:00	21/02/2023 16:30:00	00.30
Drive night	21/02/2023	21/02/2023 16:30:00	21/02/2023 17:10:00	00:40:00

i have tried to make a lot of  conditional expressions but cant get it to run properly. well cant get it to run at all.

I dont mind sending an invite so you can check the app yourself 😄

[McPolois33]

I think i am able to do this with bot automation.