Help needed with an output based on the time and date in a cell column

JasonDavis95

I attached excel data with a column for the date an Order is received in a warehouse. I want Alteryx to determine if the order was received prior to 2pm. If it was received prior to 2pm then the New Order Received Date (column would be the same date in column A. However, if the order was received after 2pm, then the new order received date would be the following business day or Order Received Date +1 (unless the order was received on a Friday after 2pm, in that case the new order received date would be the following Monday)

I'm running into issues with this formula and to get Alteryx to read just the time (HH:MM) part of the cell. 

Any advice for how I can set this workflow up would be amazing. 

Thank you!

P.S. Not sure if this helps, but I'm able to do this in excel by using this formula =IF(TIME(HOUR(A2),MINUTE(A2),SECOND(A2))<=TIME(14,0,0),A2,A2+1+IF(WEEKDAY(A2+1)=7,2,IF(WEEKDAY(A2+1)=1,1,0)))

Ord Rcv Date Sample.xlsx

OllieClarke

Hi @JasonDavis95 
Assuming all your ORD_RECEIVED_DATEs are Mon-Fri, then I think this is what you want

IF DateTimeHour([ORD_RECEIVED_DATE])<=14
THEN [ORD_RECEIVED_DATE]
//if before 2 then same
ELSEIF
DateTimeFormat([ORD_RECEIVED_DATE],'%a') = 'Fri'
THEN datetimeadd([ORD_RECEIVED_DATE],3,'day')
//If Friday then following Monday
ELSE
datetimeadd([ORD_RECEIVED_DATE],1,'day')
//Otherwise following day
ENDIF

DateTimeHour() returns the 24 hour of a datetime,
DateTimeFormat([date],'%a') returns the 3 letter weekday of a datetime
and DateTimeAdd() lets us increment a datetime by a chosen value


Hope that helps,

Ollie

dates.yxmd