Airtable Cobuilder is here! Learn more about our new no-code app creation feature, powered by AI on the Airtable Academy

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

Solved
Jump to Solution
277 0
cancel
Showing results for
Search instead for
Did you mean:
5 - Automation Enthusiast

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"
)
)
)
)
)``````

1 Solution

Accepted Solutions
8 - Airtable Astronomer

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"
)
)
)

5 Replies 5
8 - Airtable Astronomer

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

5 - Automation Enthusiast

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

8 - Airtable Astronomer

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"
)
)
)

5 - Automation Enthusiast

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?

Community Manager