Need some help converting an entire column of text into a time format

Help Activities
,
1

Hi everyone!

I’m extracting flight data from a website and putting it into an excel doc. My goal is to then format the sheet based on the Departure Time column so that the earliest flight departure is at the top and the latest departure is on the bottom.

I’m having trouble because when I open my Excel doc it won’t allow me to format the Departure and Arrival times because my Arrival/Departure times are being recognized as text instead of time.

I believe the solution is to convert the text into a Time format but I don’t know how to do it!

Example: The Departure Time entered in the cell appears as “1:30 PM”, however it isn’t recognized as a time. I need to convert this into a time format so I can then have the entire document formatted based on the earliest flight time.

2

@tmcwade12
Do you want it in excel through bot or just you need manual steps for it.

3

@tmcwade12
You’re on the right track! To format the Departure and Arrival times as time in Excel, you’ll need to convert the text to a proper time format. Here’s a step-by-step guide to achieve this:

Step 1: Insert a New Column (Optional)
To avoid overwriting the original data, you can insert a new column where you will convert the text to time.

Step 2: Convert Text to Time
Assuming your Departure Time is in column A, and you’ve inserted a new column B for the converted times, follow these steps:

  1. Select the entire column B by clicking on the column header (e.g., “B”).
  2. Go to the “Data” tab in the Excel ribbon.
  3. Click on “Text to Columns.”
  4. In the “Convert Text to Columns” wizard, choose “Delimited” and click “Next.”
  5. Leave all the delimiters unchecked and click “Next.”
  6. In the “Column data format” section, select “Time” and choose the appropriate time format that matches your original time format (e.g., “1:30 PM”).
  7. Click “Finish.”

Step 3: Sort by Departure Time
Now that you have the Departure Time column recognized as time, you can easily sort the data based on the Departure Time.

  1. Click on any cell within your data range (including the headers).
  2. Go to the “Data” tab in the Excel ribbon.
  3. Click on “Sort.”
  4. In the “Sort by” dropdown, select the Departure Time column (the new converted column if you created one).
  5. Choose “Oldest to Newest” (A-Z) if you want the earliest flights at the top.
1 Like
4

Hi Raja!

I think just the manual steps for it. I would upload my Excel doc for you to look at but since this is my first time posting it won’t allow me to upload any documents just yet.

Thank you!

5

Thank you so much! I will give this a try now and see if I’m able to get it to work.

Thanks again!

6

Do you know if it’s possible to build these conversion/sorting steps into my UiPath workflow so that when I open my Excel doc it’s already been reformatted and sorted automatically for me? Or can it only be done manually in Excel after the data has been written into the Excel doc?

Thanks again!

7

Hi @tmcwade12

  1. Use Assign activity and use below syntax:
strinput= DateTime.Parse(CurrentRow("Clock Date")).ToString("HH:mm tt")  (Datatype: System.String)
  1. Use Assign activity and use below syntax:
datvar= DateTime.ParseExact(sample,"H:mm tt", System.Globalization.CultureInfo.InvariantCulture).ToString("HH:mm tt")   (Datatype: System.String)
  1. You can use that in further process.

Refer the workflow below with sample input:

Sequence4.xaml (5.0 KB)

Hope it works!!

8

@tmcwade12 to run through Bot these steps cam be perform through by running macro script. It will bring all the changes which you made.

1 Like
9

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.