'Returning a value in a column based on a specific time range in another column
I am trying to create a function to return one of FOUR separate values(20:00-03:59 = LATE; 04:00-09:59 = MORNING; 10:00-14:59 = AFTERNOON; 15:00-19:59 = EVENING) in a new column based on a time-range from another column. For example, if the time in column "start_time" is between 04:00-09:59, I would like the function to return MORNING in column "period_of_day". Thank you.
Solution 1:[1]
A simple solution can be done using the function HOUR to extract the hour of a time column, and then use IF and AND to do the conditionals, like this:
=IF(AND(HOUR(A2) >= 4, HOUR(A2) < 10), "MORNING", IF(AND(HOUR(A2) >= 10, HOUR(A2) < 15), "AFTERNOON", IF(AND(HOUR(A2) >= 15, HOUR(A2) < 20), "EVENING", "LATE")))
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 |