Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Time Range Formula

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Solved

Jump to Solution

0
453
3

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 28, 2024 05:24 AM

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!

Solved! Go to Solution.

1 Solution

Accepted Solutions

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 28, 2024 09:56 AM

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

Reply

3 Replies 3

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 28, 2024 09:56 AM

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

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 28, 2024 11:03 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 28, 2024 07:27 PM

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!