Help

The Community will be undergoing maintenance from Friday February 21 - Friday, February 28 and will be "read only" during this time. To learn more, check out our Announcements blog post.

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

Solved
Jump to Solution
1257 0
cancel
Showing results for 
Search instead for 
Did you mean: 
chesca35
5 - Automation Enthusiast
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
Ron_Daniel
8 - Airtable Astronomer
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"
)
)
)

Screen Shot 2023-02-02 at 5.36.04 PM.png

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

See Solution in Thread

5 Replies 5
Ron_Daniel
8 - Airtable Astronomer
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?

chesca35
5 - Automation Enthusiast
5 - Automation Enthusiast

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

Ron_Daniel
8 - Airtable Astronomer
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"
)
)
)

Screen Shot 2023-02-02 at 5.36.04 PM.png

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
5 - Automation Enthusiast
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?