Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Time Formula - IF Statement

Maltaleb001
6 - Meteoroid

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:

Maltaleb001_1-1618857633112.png

And this is the formula I am using:

Maltaleb001_2-1618857723623.png

 

And this is the output:

Maltaleb001_3-1618857808209.png

 

My confusion, is why "Early" and "Morning" are Null? even other classes are working correctly! 

 

Note: The Time column is time data type.

 

4 REPLIES 4
skeen503979
8 - Asteroid

@Maltaleb001 

 

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.

skeen503979
8 - Asteroid

@Maltaleb001 

 

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.

apathetichell
18 - Pollux

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

Maltaleb001
6 - Meteoroid

Thank you for the help. it worked with all solutions provided. Appreciate the support!

Labels