Day of Week and Time Specific Question

[superjesse]

I need to label each row as Yes or No depending on whether they started an activity Mon thru Fri at 4PM, and anytime on Sat or Sun. My data has start and end time, and if they started the activity after 4pm then it should be tagged as 'Yes', or if they started anytime before 4pm then it's a 'No'. I added Day of Week to my data already, and time as 24 hours. Any help I appreciate. I'm new to Alteryx. 

Here is the code I used in SQL related product. Its basically, taking the difference between 4Pm and start time each day. I then have another column that looks at the negative vs positive number and then i flag positive as 'Yes' value.

if(DayOfWeek([d_CreateDate]) in (1, 2, 3, 4, 5),If(DateDiff("mi",Time(16,0,0,0),Time([c_BegTime]))>0,[i_TotalTime],DateDiff("mi",Time(16,0,0,0),Time([c_EndTime]))),[i_TotalTime])

[MarqueeCrew]

IF
(DateTimeFormat([date],"%u") < "6"
&
DateTimeFormat([date],"%H%M") > "1600") OR
(DateTimeFormat([date],"%u") >= "6"
THEN "YES"
Else "NO"
ENDIF

I'm not near a computer now. This or something close should work.

Cheers,
Mark

[superjesse]

Hello Mark,

Thank you so much for the quick response. I'm working on this now, and I believe it may a small syntax error but I can't get what I want.

Here is the expression I'm using, and here is the data. Any ideas? I have date and time in separate columns in my data. 

IF
(DateTimeFormat([d_CreateDate],"%u") < "6"
&&
DateTimeFormat([Begin_Time_Calc],"%H%M") > "1600") OR
(DateTimeFormat([d_CreateDate],"%u") >= "6"
THEN "PrimeTime"
Else "Non_Primetime"
ENDIF

d_CreateDate

2018-01-22 00:00:00

Begin_Time_Calc

13:49:00

Thank you very much for your help!!!

[MarqueeCrew · Accepted solution]

IF
(
(DateTimeFormat([d_CreateDate],"%u") < "6"
&&
DateTimeFormat([Begin_Time_Calc],"%H%M") > "1600")
)
OR
(DateTimeFormat([d_CreateDate],"%u") >= "6"
THEN "PrimeTime"
Else "Non_Primetime"
ENDIF


Try that.
Sent from my iPhone

[superjesse]

Awesome, it worked. Thank you so much.

[superjesse]

Hello,

Ok, your other formula worked perfectly. I just have more challenge and then I'm there.

Basically, I need to count the minutes that happened after 16:00 on Monday thru Friday. 

I have a beg time and end time.

Below is the calc I used in Spotfire.

It says if the begin time happened after 16:00 then use the 'total minutes" column I have. If begin time started before 16:00 then don't use the total minutes column..BUT calculate minutes after 16:00 until the 'end time' coilumn.

if(DayOfWeek([d_CreateDate]) in (1, 2, 3, 4, 5),If(DateDiff("mi",Time(16,0,0,0),Time([c_BegTime]))>0,[i_TotalTime],DateDiff("mi",Time(16,0,0,0),Time([c_EndTime]))),[i_TotalTime])

In the example below for the first row, I would only want to sum the minutes from 16:00 to 18:47.

My best stab at this so far, and I know I have some syntax issues.

IF
(
(DateTimeFormat([d_CreateDate],"%u") < "6"
&&
DateTimeFormat([Begin_Time_Calc],"%H%M") > "1600")
)
OR
(DateTimeFormat([d_CreateDate],"%u") >= "6")

THEN [i_TotalTime]

Else (DateTimeDiff("1600", DateTimeFormat([Begin_Time_Calc],"%H%M"),"%m")

ENDIF

[MarqueeCrew]

@superjesse,

In order to answer your question, it would be helpful to understand the data coming into your desired formula in terms of your Alteryx input.  Here's a guide to what I suggest and how to create some sample data.

Cheers,

Mark

[superjesse]

Mark,

Thanks for the video - it helped.

I found a way to make my question much more clear. I simply want to do the difference between a time column and a specific time (16:00).

How do I write in a specific time? The 16:00 for example.

(DateTimeDiff([End_Time_Calc],"16:00", "minutes")

[MarqueeCrew]

@superjesse 

Can you please join me now for a webex?

https://meetings.webex.com/collabs/meetings/join?uuid=M9H390I2TLCQW8TWTM30Y2451B-UCSL&epwd=bdc1450e4...

Cheers,

Mark