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

[OllieClarke · Accepted solution]

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

[JasonDavis95]

@OllieClarke  Thank you! 

I forgot to add this into my question, but would I need to add another parameter to the formula if I also wanted to remove Holidays the same way you did with weekends? If the ORD_RECEIVED_DATE falls on a Holiday then the New Order Received date would be the following business day. 

For this example we can use 5/27/24 and 6/19/24 as the Holidays.