How to extract the date format from excel correctly?

Dears,

Am trying to extract a date format correctly base on excel format, says in excel list all start from dd/MM/yyyy, but extracted out data to email somehow would show MM/dd/yyyy 00:00:00

from excel
image

to output email
image

how to let the robot to read the format correctly and remove the time 00:00:00 at the back.

Regards,

recap the expression input

Example, i input this to capture excel expiry date row to get the list of date.

Expiry Date: "+row(“Expiry Date”).ToString

Try this:

Expiry Date: "+row("Expiry Date").ToString("dd/MM/yyyy")

Hi @Alfred_Gan

How about this expression?

CDate(Row("Expiry Date").ToString)Tostring.("dd/MM/yyyy")

Or

DateTime.ParseExact(Row("Expiry Date").ToString","d/M/yyyy hh:mm:ss",System.Globalization.CultureInfo.InvariantCulture).ToString("dd/MM/yyyy")

d/M/yyyy → Date format that in Excel file

Regards
Gokul

Hi @moosh Thank you for your feedback, but i have get an error for this by adding (“dd/MM/yyyy”) at the back.

image

Hi Gokul,

Error for this expression input.

Is there any alternative way?

Regards,

Hi @Alfred_Gan

Can you share the error screenshot

CDate(CurrentRow("Expiry Date").ToString).ToString("dd/MM/yyyy")

Regards
Gokul

Hi @Gokul001,

the expression input is like this

“Dear Sir/Madam,

This is an alert to request a quotation on the following items:

Description “+row(“Description”).ToString+”

Expiry Date: “+ CDate(CurrentRow(“Expiry Date”).ToString).ToString(“dd/MM/yyyy”)+”

Serial Number: “+row(“Serial No.”).ToString+”

This is an automated email.

Regards,
Email Automation Robot”

and error encounter as below
image

Regards,

Hi @Alfred_Gan

Your Requirement is Getting the Correct Data in the excel file Right?

Now You have New Question?

Regards
Gokul

Hi @Gokul001

Yes i have got the correct date list in the excel,

i could extracted out and send email in body. but somehow the date is showing MM/dd/yyyy instead of dd/MM/yyyy

Regards,

Hi @Alfred_Gan

Have you tried with this expression

DateTime.ParseExact(Row("Expiry Date").ToString","d/M/yyyy hh:mm:ss",System.Globalization.CultureInfo.InvariantCulture).ToString("dd/MM/yyyy")

Regards
Gokul

Hi @Gokul001

Yes i tried to input these into my current expression in addition.

expression:
Dear Sir/Madam,

This is an alert to request a quotation on the following items:

Description +row(“Description”).ToString+“

Expiry Date: “+DateTime.ParseExact(Row(“Expiry Date”).ToString”,“d/M/yyyy hh:mm:ss”,System.Globalization.CultureInfo.InvariantCulture).ToString(“dd/MM/yyyy”)+”

Serial Number: “+row(“Serial No.”).ToString+”

This Is an automated email.

Regards,
Email Automation Robot"

Regards,

expression.txt (423 Bytes)

:ambulance: :sos: [FirstAid] Datatable: Debug & Analysis invalid DateTime Strings / String to DateTime Parsing Issues - News / Tutorials - UiPath Community Forum

Hi @Alfred_Gan

Try with this one

"Dear Sir/Madam,<p>This is an alert to request a quotation on the following items: <p>Description" +row("Description").ToString+"<p>Expiry Date: "+DateTime.ParseExact(row("Expiry Date").ToString,"d/M/yyyy hh:mm:ss",System.Globalization.CultureInfo.InvariantCulture).ToString("dd/MM/yyyy")+"<br>Serial Number: "+row("Serial No.").ToString+"<p><strong>This Is an automated email.</strong><p>Regards,<br>Email Automation Robot"

Or

"Dear Sir/Madam,<p>This is an alert to request a quotation on the following items: <p>Description" +row("Description").ToString+"<p>Expiry Date: "+CDate(row("Expiry Date").ToString).ToString("dd/MM/yyyy")+"<br>Serial Number: "+row("Serial No.").ToString+"<p><strong>This Is an automated email.</strong><p>Regards,<br>Email Automation Robot"

Regards
Gokul

1 Like

Hi @Gokul001,

It works! thank you for the guideline and solution!

Regards

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