Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Feb 02, 2023 06:32 AM
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."
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"
)
)
)
)
)
Solved! Go to Solution.
Feb 02, 2023 02:58 PM
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"
)
)
)
Feb 02, 2023 08:07 AM
Chesca, is the "Time" field saying what time of day they took the test? Or is it saying how long their test lasted?
Feb 02, 2023 12:44 PM
Hey, Ron! Thanks for the reply. "Time" is the time they started the test NOT the length the test was.
Feb 02, 2023 02:58 PM
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"
)
)
)
Apr 12, 2023 06:29 AM
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?
Jun 06, 2024 10:39 AM
@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/...