Format date in excel - dd.mm.yyyy

Help StudioX
,
1

Hi, my robot takes a date from excel and pastes it into an email. The date is in the format dd.mm.yyyy, but the robot converts this date to the format: mm/dd/yyyy - how can I change it? I need the format dd.mm.yyyy

2

Hi @sullivanne

 DateTime.ParseExact(dateFromExcel.ToString, "MM/dd/yyyy", System.Globalization.CultureInfo.InvariantCulture).ToString("dd.MM.yyyy")
3

Hie @sullivanne
try this

The image shows a code snippet using to convert and display the date "05/12/1990" in "dd.MM.yyyy" format, resulting in a message box displaying the date as "12.05.1990". (Captioned by AI)
The image shows a code snippet using to convert and display the date "05/12/1990" in "dd.MM.yyyy" format, resulting in a message box displaying the date as "12.05.1990". (Captioned by AI)811×303 11.2 KB

datetime.ParseExact(“05/12/1990”,“MM/dd/yyyy”,system.Globalization.CultureInfo.InvariantCulture).ToString(“dd.MM.yyyy”)

Cheers Happy Automation

4

Hi @sullivanne

Store the excel data in a string variable called Excel_Date. Then use the below datetime expression to convert to the required format.

- Assign -> Excel_Date = DateTime.Parseexact(Excel_Date.toString, "mm/dd/yyyy", System.globalization.Cultureinfo.InvariantCulture).to string("dd.mm.yyyy')

Hope it helps!!

1 Like
5

Thank you, but I have a problem with putting the datatable into excel, because it is a string. What can I do to change the date format and be able to use excel.

6

Your query is quite confusing could you be more specific… @sullivanne

If possible try to share the images for our better understanding. Input and Expected output.

1 Like
7

@sullivanne

Read the excel data into a datatable variable
Use for each row in datatable

Assign: Excel_Date = DateTime.ParseExact(CurrentRow("DateColumnName").ToString(), "MM/dd/yyyy", System.Globalization.CultureInfo.InvariantCulture).ToString("dd.MM.yyyy")
Assign: CurrentRow("DateColumnName") = Excel_Date

Write range workbook

1 Like
8

Check your machine date format and change it

1 Like
9

Hie @sullivanne here
change build data table with Excel application scope so you get the data from Excel

image
image803×121 8.3 KB

The image shows a "Build Data Table" window containing a column titled "Dates (String)" with four dates listed: 05/25/2024, 05/26/2024, 05/27/2024, and 05/28/2024. (Captioned by AI)

after use for each row actvity
This image shows an RPA (Robotic Process Automation) workflow for iterating through a data table, extracting and converting date values, and writing the converted dates to a log. (Captioned by AI)

use two assign activity
1 hold the value of the column data and store in a variable form
image

2 Assign help you to convert your date format into your desired format

image
image830×96 6.16 KB

And finally your Output looks like this
The image shows an output log from a debugging session, indicating the start and end of a 'Forum execution' along with several dates in May 2024. (Captioned by AI)

Cheers Happy Automation…

1 Like
10

Hi @sullivanne,

Please parse the date from excel and format it the way you wanted as below.

DateTime.ParseExact(strExcelDate.ToString, “MM/dd/yyyy”, System.Globalization.CultureInfo.InvariantCulture).ToString(“dd.MM.yyyy”)

Regards,
Ranjith Udayakumar

1 Like
11

Thank You very much, but I have error: String ‘7/31/2023’ was not recognized as a valid DateTime.

12

Okay @sullivanne

Try the below expression,

DateTime.ParseExact(DateCol, {"MM/dd/yyyy","M/dd/yyyy","M/d/yyyy"}, System.globalization.Cultureinfo.InvariantCulture, System.globalization.DateTimestyles.None).toString("dd.MM.yyyy")

Hope it helps!!

13

Hie @sullivanne can you show your date format sample that have stored in excel