Bring your best ideas to the AI Use Case Contest! Enter to win 40 hours of expert engineering support and bring your vision to life using the powerful combination of Alteryx + AI. Learn more now, or go straight to the submission form.
Start Free Trial

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Convert Text to 24 Hour Time

ISUGraber
8 - Asteroid

I was able to partly do this but I realized that the information I am working with has a lot more variety to it than I thought.  So I am currently trying to convert data that can come as one of these forms:

 

Below is what i have.  What i want to do is capture the Delivery Window Open and Close times and turn them into a time value and a 24 hour time at that.  If there is no closing window then add 3 hours to the open window and make that the close window.  

 

If the value is N/A or blank please just return midnight.

 

Delivery Window
9am-11am
11am-2pm
9am-11am
9:00 AM
11:00 AM
12:00pm
9am
4-5pm
9am
11-12pm
9:00 AM
10:15 AM
11:30 AM
12:30 PM
2:00 PM
5:00 PM
1:00 PM
9:30 AM
1500-1900
0900-1200
9:00 AM
10:30-12:00
14:00-17:00
1000-1600
0900-1100
0900-1700
0900-1200
12:30-13:30
15:00-17:00
9:00 AM
10:00 AM
12:00 PM
0800-0900
0900-1000
1030-1130
1200-1400
1300-1500
11:00 AM
4:30 PM
10:00 AM
1:00 PM

4:00 PM

N/A

 

Below is a screenshot of what I'm doing.  This only works on the 9am-10pm.  

 

AlteryxDeliveryWindow.png

 

1 REPLY 1
danilang
19 - Altair
19 - Altair

Hi @ISUGraber 

 

Here you go

 

danilang_0-1619876538008.png

 

The Multi-Field formula tool has an if-then-else that handles all the variations in your input data for the first and second times.  The Formula tool fixes the 2nd time if it ends up being 00:00 as required.  It could have been completely done in the Multi-Field tool but the formula in there is scary enough as it is

 

danilang_1-1619876784462.png

 

 

Dan

Labels
Top Solution Authors