Getting a number based on time

[Kjell_Pettersen]

Hi, all.

I thought I had it figured out but turns out I was wrong…
I have two tables:

• Main table
• Time table
  In the Time table I have cutoff times that correspond to points in increments of 0.5, like 00:18:01 = 0.5 points, 00:19:01 = 1 point, etc. So, the idea is for the Main table to show 0.5 points between 00:18:01 and 00:19:00 and then shift to 1 point from 00:19:01,
  (And if there is a way to omit the unnecessary hours, I highly appreciate any suggestion)

The formula I thought worked for the Main table is

DB Poengtrekk: cut-off times and points database
Minutter: Time/minutes column in the points database
Tidsbruk: Time spent, from the Main table
Poengtrekk: Points column in the points database

The idea is that the time table is filtered based on the time and returns the corresponding point. The problem is that instead of changing to 1 point from 00:19:01, it changes already from 00:18:02.

Is there a way to solve this in a fairly easy way?

I apologize for not being able to share the database, it is a bit large and already contains PII.

[Christiaan_Huizer]

hi @Kjell_Pettersen ,

Can you try this?

[DB poengtrekk].Filter(
Minutter.ToTime() >=
Tidsbruk.ToTime()
).Poengtrekk.First()

By the way it should not be to difficult to create a sample doc with just a few tables?

Cheers, Christiaan

[Kjell_Pettersen]

Thank you, @Christiaan_Huizer. Unfortunately, that formula does the exact same thing.
I played around a bit with using two time columns, From Time and To Time, to see if that would work better but I am not able to find a formula that can verify if a given time is found between the two, and then provide a corresponding number from the third column.

Since the use case is not relying on very frequent updates or changes, I decided to go with a “simple” SwitchIf() formula instead of racking my brain trying to come up with a complex solution to a simple problem…

[Christiaan_Huizer]

@Christiaan_Huizer

[DB poengtrekk].Filter(
Minutter.ToTime() >=
Tidsbruk.ToTime()
).Poengtrekk.First()

I forgot to be explicit about the thisRow which is crucial.

[DB poengtrekk].Filter(
Minutter.ToTime() >=
thisRow.Tidsbruk.ToTime()
).Poengtrekk.First()

and please take 5 minutes to share a demo doc, cheers, christiaan

[Kjell_Pettersen]

Thanks, Christian!

We just concluded this year’s event so now I have nearly a year to look into these kinds of details (and more…).
I will try to provide an example doc soon.

[Kjell_Pettersen]

Here is a simple example.
The Points table lists the Start time and End time for each penalty point. It can also just show the Start time if that makes more sense.

The Time table shows how much time each participant has spent and the number of points that should be deducted.
In the example, Participant 2 and 3 should have respectively 1 and 2 points deducted, not 2 and 3 points. And Participant 1 should not have any points deducted.

[Christiaan_Huizer]

hi @Kjell_Pettersen , can you adapt the sharing settings so we can see, access and edit the content?
cheers, christiaan

[Kjell_Pettersen]

It should be available now.

[Christiaan_Huizer]

thx @Kjell_Pettersen , can you create a table that ‘hard coded’ shows what the expected outcome is, so I have a reference?

[Kjell_Pettersen]

I see that you have already commented and edited. And solved, by the looks of it…

Thank you, Christian!

[Christiaan_Huizer]

yes: this is the code snippet:

Points.Filter(
[Start time] <= thisRow.[Time spent]
).[Penalty points].Last().IfBlank(0)

The great Coda connaisseur @joost_mineur reached out and wondered how stable my solution was. I had to admit that using a start & end time is not a good practice and that without sorting properly you may mess up as well. The end time is error sensitive, so you best use only the start time, that will do. Sorting is crucial, always;

below how that looks:

The filter shared will only do in a well drafted context, this is more robust.

Cheers, Christiaan