Time with 24:00

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!

image.png

Date Time

Accepted answers

bergaz

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

All comments

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!

24 to 0.yxmd

philipmannering

Hi @bergaz,

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

Let me know if it works!

24 to 0.yxmd

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

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

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335