Skip to main content

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

 

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

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.

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

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?

@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/ba-p/186645

Reply


Terms & Conditions