Help

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

Solved
Jump to Solution
273 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

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

4 Replies 4
Ron_Daniel
7 - App Architect
7 - App Architect

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

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

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

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?