Time with 24:00

[bergaz]

Hello!  I have a CSV input that contains a date and time column and I'm easily able to convert that from a string to an Alteryx recognized date/time by using the DateTime tool except for the midnight hour.  The issue seems to be the input format of "2023-01-01 24:00:00" because all dates convert except the "24:00:00" which are [Null]. 

I'm looking for a suggestion to convert all my data to a date/time without having to adjust the CSV prior to input.

Thank you!

[PhilipMannering]

Not sure what format the time comes in as, but you probably want to replace all your 24:00s with 00:00s. But feel free to send a few rows of data so I can test.

[bergaz]

Hi Phillip.  The data comes in this format and it's an automated download from a website so I'd rather not have to split the workflow up such that I'd have to locate the file and edit it before finishing the analysis in Alteryx.  I've attached an example of what I'm seeing.  Thank you!

[PhilipMannering]

Hi @bergaz,

Of course, no need to edit the download beforehand. See attached a potential solution.

Let me know if it works!

P

[RobertOdera]

Hi, @bergaz 

Alteryx starts the counter from 0 and not 1, i.e., a day would run from  0 to 23 hours, where zero-hour is equivalent to the conventional 24-hour.

Therefore, change the 24:MM:SS in your input to 00:mm:ss (and afterward, in your final output, change the 00:mm:ss to 24:MM:SS).

I hope you find this helpful - cheers! 

[bergaz · Accepted solution]

Thanks for all of your input but I believe I've found the solution that works best for my situation.  One of the key issues is that a date such as this:  1/1/2023 24:00:00 would need to convert to 1/2/2023 00:00:00.  The following formula is what I'm using and it works well:  

if right([Field],8) = '24:00:00' then DateTimeAdd (todatetime (left ([Field], 10)),1,'Days') else [Field] endif