Time Range Formula

[David_Weigle]

Hi everyone,

I'm looking to create a formula where, given a specific day of the week, and a time range, the formula will generate an output, and then write a script where it will take the output and enter it into a Linked Field box. 

For example, on Wednesdays between 7:20am to 8:00am, I want to generate a result that says "Period 1." 8:06am - 8:50am would be "Period 2," and so on, so forth. We have different bell schedules for different day of the weeks. 

And then, once it generates the result, I want to have a script run to add the "Class Period" into the empty Class Period box. 

Is this possible? 

Thanks in advance for any help that could be provided!

[Pascal_Gallais- · Accepted solution]

Hello David,

I have a solution that seems to work with formulas and an automation. It is a bit complex and requires to spend some time on setup. Maybe someone will come out with an easiest solution.

Here is the result that I get after running the automation:

- Step 1 - Table Periods

This table contains one line for each period to consider (reason why there is a lot of setup to do):

For each period, we set the day, the starting point separated in two number type fields: one for the hour, another one for the minutes and same thing for the ending point.

Constraints to respect:

- hours must be expressed from 0 to 23

- Two periods for the same days must never overlap

-Step 2- Formulas in table period

We had two formulas to compute starting and ending points in minutes:

-Step 3- Formulas in table "Shifts"

Table Shifts in this example is the one where we want to bring down the period information.

We had 3 formulas: Hour, Minute and Minutes

- Step 4- Link table "Shifts" to table period

- Step 5- Automation

You will need to decide what the trigger is. For my example I added a checkbox field "Test" in table "Shifts", this field being the trigger.

First action is to find the corresponding record in table Period (we do not want to find more than one record, reason why you must make sure that Periods for a given day do not overlap).

We then add a conditional action to update only if exactly 1 record has been found:

And we update the record in table shift:

Where "List of Period_id" is set a follows:

This will bring down the period primary key into table Shifts, meaning that you can then add some lookup field such as the period name.

Regards,

Pascal

[David_Weigle]

Thank you! I will start looking at putting this together!

[David_Weigle]

I ended up using a good chunk of your solution--and it worked! Ultimately, we're using this to try and track Student Tardies by Class Period. I ended up creating a formula on our "Tardy" table that calculated the Minute of Day for the tardy, then using ranges for each date. It actually wasn't that difficult to set up once I got it going. 

Thanks for your help!!! I greatly appreciate it!