Data Extraction with Date&Time not working as expected

It is fairly simple but excel date sorting not working as expected, so it made me to seek help from you experts.
There are 3 web pages with tabular data having a due date column in DD/MM/YYYY 12:00:00 AM format (time part is always fixed), now bot to extract all 3 web pages data and create separate sheets in one excel, merge all 3 sheets into one sheet and sort the data on due date ascending order, the issue is when bot extract the data and create an excel sheet, this date value is not consistent, for few items it removes time and for few other time value stays.
I think it is excel issue as bot extract data correctly, is there any way I can keep this date value consistent throughout the process?

I hope i made the issue clear, please do let me know if you need additional details, besides following screenshots are to support
Due date on webpage 1
image
Now when above data is copied in excel
image
Same thins is happening with other webpages data too and as a result final sorted data is not in accurate order.

Any input/thoughts will be helpful.

@ramvashista85
Try this
DateTimeFormat.zip (20.0 KB)

Thank you @Karuna for your response, appreciate, i tried this before but didnt work, tried it again but unfortunately didnt work too. problem is while creating data in excel, date time converts to MM/DD/YYYY. please refer following-
Data extracted from webpage

image

Data saved in excel
image

Any other thoughts please?

  1. Try both, the standalone “write range” as well as the “write range” inside the excel scope. The behavior is different.

  2. If you’re using an excel template to fill the data, make sure you have the correct date formatting in the column

1 Like

@ramvashista85

check below for your reference

also for difference

Hope this may help you

Thanks

@ramvashista85
You need to set the Date and Time format in excel
Date Column – Right Click – Format Cell and refer the screen shot

Then try with attached sample workflow it will work.

Thank you @nameless, using discrete write range did the trick.
Never imagined different behavior of same activity when used standalone and inside excel scope.

I managed to solve this.
The issue was with excel, date and time in excel is quite funny to work with. As I mentioned in problem statement, date values extracted from webpage are not consistent, for some of it comes with dd/MM/yyyy and for other MM/dd/yyyy, for some items time is truncated.
So the challenge was to keep the data consistent. Please let me know if someone need to know the solution.

Hello, i have exactly the same issue. Can you explain how did you do to resolve it ?
thank you in advance :slight_smile:

@DjamBen, I assume you have data in multiple excel worksheets and you want to merge all the sheets data into one final sheet, please follow below steps

  1. Extract data from source, let’s say web page, add this data in excel using Write Range activity (dont add write range inside excel scope)
    image

  2. Use Excel Application Scope and read all sheets created from step 1 by using Get Workbook Sheets activity, this would give you output of type list, this means you have all your worksheets into this list variable.
    image

  3. Read all sheets data using for-each loop and use Read Range activity and use Merge Data Table to have all sheets data into a datatable
    image

Here you would get data in the data table obtained from step 3, let’s say dt_outputDataTable.

  1. Now format DateTime value of above data table to read only date value, use for each to read each and every row. Remember, date time is string here and you need to convert it into date, refer following screenshot-

  2. Use Sort Data Table activity to sort data table obtained from step 3, this will have your data sorted on date column, and finally use Write Range activity to create final sheet.
    image
    Properties ofSort Data Table
    image

image

Hope this helps you. Let me know how it goes.

1 Like

thank you @ramvashista85 for explaining in detail, it works perfectly.

I also found another solution, which is to execute a VBA macro (Invoke VBA) which will set the data column format, and sort the data after that. herewith the vba lines>>

Sub TextToColumns()
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A:A"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 4), TrailingMinusNumbers:=True
    
    Range("A:F").Sort Key1:=[A1], Order1:=xlDescending, _
    Header:=xlYes
End Sub
1 Like

Glad it worked @DjamBen
Happy automation