Hi everyone,
I am trying to use IF with time but with no luck. I am not sure what i am missing. I have Time clumn and wanted to create new column that classifies day times. For example, from 4am - 8am will be "Early".
This the data set:
And this is the formula I am using:
And this is the output:
My confusion, is why "Early" and "Morning" are Null? even other classes are working correctly!
Note: The Time column is time data type.
Solved! Go to Solution.
You might need to separate your timeframes so they are not the exact same. Meaning, if you want it to display "Morning" then set the timeframe to "8:00:01" etc. Your formula looks correct.
Another (more clunky and tedious) option would be to filter the data per timeframe and add your label to the column "Period" column. Then use the Union tool to tie them all back together again and the Sort tool to arrange Ascending.
Actually, upon looking further, your problem might simply be that you need to add a zero before the hour (04:00:00 and (08:00:00) in "Early" etc.
I can confirm that to have a time compare you need to have a 0 in front of your time. Also - you should be using null() not "null" or you can use "" for empty.
Here's the simplified formula using a switch instead of a nested if...
switch(floor(tonumber(datetimeformat([Time],"%H")
)/4),null(),1,"early",2,"morning",3,"midday",4,"evening",5,"night")
Thank you for the help. it worked with all solutions provided. Appreciate the support!