Time with 24:00
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
Solved! Go to Solution.
- Labels:
- Date Time
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @bergaz,
Of course, no need to edit the download beforehand. See attached a potential solution.
Let me know if it works!
P
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
![](/skins/images/D34B41DA407DC996E7BFF253AD24F7E2/responsive_peak/images/icon_anonymous_message.png)