Working with Times

[MrBradB]

I am working on a database for a daycare. I have a table called Time Sheets with fields Arrival (date), Departure (date) and Duration (duration). (There are other fields in this table to link to each child.)

I have a second table called Meals with fields Name, Start Time and End Time (currently they are all single line text). This table has 4 records:

Breakfast, 7:30am, 8:35am
Morning Snack, 10:30am, 11:00am
Lunch, 12:15pm, 12:45pm
Afternoon Snack, 4:00pm, 4:30pm

I want to determine if a child qualifies for each meal based on when they arrived and departed. If the child is present when each meal is served, they qualify. What is the best way to do this? I am still in the early building stages for this database, so if I need to reorganize tables and such, now is the time.

Thanks in advance,
Mr. Brad B.

[TheTimeSavingCo · Accepted solution]

Hmm, if the meal timings will never change, it might be easier to have the meal timings within the Time Sheets table instead

If the meal timings do change and you want to keep a historical log of whether a specific child on a specific date qualified for that day's meal timings, then yeah keeping it in a separate table would make sense, and you'd need to link the 4 meal records to every "Time Sheets" record (probably via an automation), and then create lookups to grab the start and end times for each meal

---
Formula wise, you'd need to parse those times for the arrival day, and use combinations of IS_BEFORE, IS_AFTER and IS_SAME to figure out whether they're applicable for that timing

The formula gets pretty involved, and here's my attempt at it

Link to base 

IF(
  AND(
    OR(
      IS_SAME(
        {Breakfast Start Time},
        Arrival,
        'minutes'
      ),
      IS_BEFORE(
        Arrival,
        {Breakfast Start Time}
      )
    ),
    OR(
      IS_SAME(
        {Breakfast End Time},
        Departure
      ),
      IS_AFTER(
        Departure,
        {Breakfast End Time}
      )
    )
  ),
  "True",
  "False"
)