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.
CodePudding user response：
You really don't need your current Column E at all (unless you just like seeing that time isolated). It's not necessary for the Col-F formula's use.
For now, delete all of Column F (including the header) and paste the following in F1:
=ArrayFormula({"period_of_day";IF(D2:D="",,IFERROR(VLOOKUP(MOD(D2:D,1),{TIME(0,0,0),"LATE";TIME(4,0,0),"MORNING";TIME(10,0,0),"AFTERNOON";TIME(15,0,0),"EVENING";TIME(20,0,0),"LATE"},2,TRUE)))})
This will produce the header and all results for all rows.
As I say, after this formula is in your sheet, you really can just delete Column E altogether as it is unnecessary. And with the amount of data you're dealing with, you should keep things as trim as possible for best results (including deleting unused columns like Col I and Col J as well).
CodePudding user response：
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")))