Dateformat

Hi all,
I am stuck at a point.Please help me
I have a text file, I am converting it to Excel.
I have a column by name TrDate with 1000 rows till last year 31st the date was come as dd-MM-yyyy and BOT was running as expected.
But from yesterday the date is coming as d/M/yyyy but BOT is reading it as 1st February 2024.
I want the BOT to read it as 2nd January 2024.

After reading I need to filter the date with present date.

Please help me

@0bb4628e217fd43ac86ac9294

How are you trying to parse the date?

cheers

Hi @Anil_G I am not getting it

Hi @0bb4628e217fd43ac86ac9294

You can use format cells to convert the whole date format in excel

or

while reading the date you can format it. by using assign where Tr_date is your date from excel.

formatted_date = CDate(Tr_Date).ToString(“dd/MM/yyyy”)

Hope this helps :slight_smile:

Hi @0bb4628e217fd43ac86ac9294

If possible could you share the excel file with dummy data.

Hope it helps!!
Regards

Hi @0bb4628e217fd43ac86ac9294 ,

  1. Are you downloading file from any source? If yes please check the date format in the application like sap or some other.
  2. Are you creating/manuplating data with any excel (VBA or macro’s) check the date format from the machine side.

Apart from are you using any other method, try to elabrate the question, so we can help you.

Thankyou,
Pavan Kumar/Techystack

Hi @Pavan_Kumar177 I am downloading file from an application.
It comes in .txt format and I am converting text to Excel and getting the output as above picture

I am not manipulating data. My machine date format is 1/2/2024 that is M/d/yyyy

Hi @0bb4628e217fd43ac86ac9294

You need to convert the date format as dd-MM-yyyy

CODE:

Dim dt As DataTable = dt_Input

For Each row As DataRow In dt.Rows
    Dim dateString As String = row("Dates").ToString()
    
    ' Check if the date format is "d/M/yyyy"
    If DateTime.TryParseExact(dateString, "d/M/yyyy hh:mm:ss", CultureInfo.InvariantCulture, DateTimeStyles.None, Nothing) Then
        Dim originalDate As DateTime = DateTime.ParseExact(dateString, "d/M/yyyy hh:mm:ss", CultureInfo.InvariantCulture)
        row("Dates") = originalDate.ToString("dd-MM-yyyy")
    End If
Next

Input:
image

Output:
image

Sequence7.xaml (12.4 KB)

Cheers!!

ok, now it’s converting?

If you want to filter the dates with present date then use the below workflow

Output:

image

Cheers!!

@0bb4628e217fd43ac86ac9294
Glad to be helped…If no more questions can you please close the topic…else happy to help

1 Like

@lrtetala Thanks a lot for your help but in my system after doing the Invoke code still it is writing as 2/1/2024 and BOT is reading as 1st February 2024

@0bb4628e217fd43ac86ac9294

Can you please share your sample excel file and it’s no problem if dummy data you can provide

@0bb4628e217fd43ac86ac9294

Can you please extract the below file and replace your excel file and run the bot

BlankProcess14.zip (946.1 KB)

Cheers!!

@lrtetala Its still coming as 2/1/2024

@0bb4628e217fd43ac86ac9294

Can you please share your excel file not an image and delete the confidential data then send

Hello @0bb4628e217fd43ac86ac9294
It seems like the input date format was changed to MM/dd/yyyy from dd/MM/yyyy and your robot is handle like FEB 1.
So you need to swap it to the format that you expected like @lrtetala showed you above, but in this way (changing the input date format):

If DateTime.TryParseExact(dateString, "M/d/yyyy hh:mm:ss", CultureInfo.InvariantCulture, DateTimeStyles.None, Nothing) Then
        Dim originalDate As DateTime = DateTime.ParseExact(dateString, "M/d/yyyy hh:mm:ss", CultureInfo.InvariantCulture)
        row("Dates") = originalDate.ToString("dd-MM-yyyy")
    End If

Note: Other way is probably using the new activity to format the date to text on v23.10 within a loop.

Hope this was helpful.

Thank you @lrtetala @Pavan_Kumar177 for your help my code is working fine now.

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