Converting DateTime from Excel to DateTime in Alteryx and formatting rows

Question

I have a month data with datetime and wishes to remove empty rolls (with criteria). As 6/4 falls on a Saturday, I wish to omit the data out and would like to do so for all the weekend (Saturday and Sunday). I can simply remove rows with null but however, by removing nulls, I will be removing some of the data during the weekdays which I want it to be within the data. Furthermore, my data runs 24 hours with 30 minutes interval. I would like to take only a specific timeframe (e.g. 8am to 6pm).

Snippet of data:

DateTime    2022-06-01 00:08:00    2022-06-01 00:08:30xxxx2022-06-01 00:09:00xxxx2022-06-01 00:09:30xxxx2022-06-01 00:10:00xxxx2022-06-02 00:08:00    2022-06-02 00:08:30xxxx2022-06-02 00:09:00xxxx2022-06-02 00:09:30    2022-06-02 00:10:00xxxx2022-06-03 00:08:00xxxx2022-06-03 00:08:30xxxx2022-06-03 00:09:00xxxx2022-06-03 00:09:30xxxx2022-06-03 00:10:00xxxx2022-06-04 00:08:00    2022-06-04 00:08:30    2022-06-04 00:09:00    2022-06-04 00:09:30    2022-06-04 00:10:00

Edited: The date format

Another problem regarding datetime is that when I first import it into Alteryx, the system reads the datetime as 6/1 8:00 am and when I use the DateTime method in Alteryx, it did not work and become null.

The datetime in Excel file is in Custom format. Any suggestion to convert this to DateTime in Alteryx? Thank you!

binu_acs · Accepted Answer

@kv-defy one way of doing this with the string functions. you can filter the data based on weekends and time

adding the filter conditions as well

grazitti_sapna · Accepted Answer

@kv-defy, another way of solving this problem.

I hope this helps!

Thanks!

grazitti_sapna · Accepted Answer

@kv-defy, then you can use the if-else condition if your DateTime format is in mm/dd hh:mm am/pm then it will convert the date into alteryx format else it will leave the DateTime as it is.

if REGEX_Match([DateTime], "\d+/\d+\s\d+:\d+\s\w+") then DateTimeFormat(DateTimeNow(),"%Y-")+Datetimeformat(DateTimeParse([DateTime],"%m/%d %I:%M %p"),"%m-%d %H:%M:%S")
else [DateTime] endif

Thanks!