Solved

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

Forum|Forum|2 years ago
February 2, 2023
5 replies
31 views

+2

chesca35
New Participant

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

Best answer by Ron_Daniel

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

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

+21

Ron_Daniel
Inspiring
Forum|Forum|2 years ago
February 2, 2023

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

Like

C

+2

chesca35
Author
New Participant
Forum|Forum|2 years ago
February 2, 2023

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.

Like

+21

Ron_Daniel
Inspiring
Answer
Forum|Forum|2 years ago
February 2, 2023

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

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

Like

C

+2

chesca35
Author
New Participant
Forum|Forum|2 years ago
April 12, 2023

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

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?