Can I Create a Single Field Select That Automatically Populates Based On a Formula?

[chesca35]

Hey, there! 🤗

I have a column named "Time" that has the time a test was taken based on military time.  I'm trying to get the the column "Time of Day" to be able to populate automatically either "morning," "afternoon," or "evening."

I believe I need an IF statement.

If score time is:
0-11:59: morning
12-16:59: afternoon
16:59-24:00: evening

My formatting is probably way off 🤣 I am very new at formulas.  This was based off my research in other forums.

Thanks!

Here's what I tried:

IF( {Risk Score}  < 0, 
  "error, value too low", 
  IF( {Time} < 12:00, 
    "morning", 
    IF( {Time} < 17:00, 
      "afternoon", 
       IF( {Time} <= 24:00, 
         "evening", 
          IF( {Time} > 24:00, 
            "error, value too high"
        )
      ) 
    ) 
  )
)

[Ron_Daniel]

Chesca, is the "Time" field saying what time of day they took the test? Or is it saying how long their test lasted?

[chesca35]

Hey, Ron! Thanks for the reply.  "Time" is the time they started the test NOT the length the test was.

[Ron_Daniel · Accepted solution]

Thanks for clarifying! Here's my solution if the appointments are in a Date field:

IF({Test Date/Time}=BLANK(),
"",
IF(
DATETIME_FORMAT({Test Date/Time}, "HH")<12,
"Morning",
IF(
DATETIME_FORMAT({Test Date/Time}, "HH")<17,
"Afternoon",
"Evening"
)
)
)

If you have JUST a time field, you'll need to set that field format to Duration. And duration is stored in seconds, so the formula would be

IF(
{Time}="",
"",
IF(
{Time}<43200,
"Morning",
IF(
{Time}<61200,
"Afternoon",
"Evening"
)
)
)

[chesca35]

Thank you SO much! I really appreciate your help. 🙂 @Ron_Daniel do you know if I can turn these times into a single select but still keep it in a formula?

[Rosalind_Lutsky]

@chesca35 @Ron_Daniel wanted to share a new feature update related to your question: https://community.airtable.com/t5/announcements/introducing-single-select-output-for-formula-fields/...