Date conversion from numbers to date format

Help Studio
1

Hi,

I have extracted dates in numeric format for example: 20240309 (yyyyMMdd). How can I convert them to date format, for example 09.03.2024 (dd.MM.yyyy).

Thanks in advance for your help.

2

Hi @maciek

Store the extracted data in a variable called Input. Then use the below date time manipulation expression to convert it to required format.

- Assign -> Input = "20240309"

- Assign -> Output = DateTime.ParseExact(Input.toString, "yyyyMMdd", System.globalization.Cultureinfo.InvariantCulture).toString("dd.MM.yyyy")

Hope it helps!!

3

@maciek ,

Please use this expression Assign activity:

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

4

I followed your advice. My expression looks like this: DateTime.ParseExact(CurrentRow(“Start”).ToString(), “yyyyMMdd”, System.Globalization.CultureInfo.InvariantCulture).ToString(“dd.MM.yyyy”)

But I am getting an error: Assign: String ‘’ was not recognized as a valid DateTime.

5

Hi @maciek

Can you try with Format Cells activity

Regards,

6

Instead of the current row value directly assign it in an assign activity and pass that variable

7

Your Output variable should not be DateTime datatype variable it is String datatype… @maciek

If you want to convert the value to datetime datatype, follow below,

- Assign -> Var_DateTime = DateTime.ParseExact(Input.toString, "yyyyMMdd", System.globalization.Cultureinfo.InvariantCulture)

If you want to get the output in specific format as you asked in your question the output variable should be in string and use the below expression,

- Assign -> Var_Str = DateTime.ParseExact(Input.toString, "yyyyMMdd", System.globalization.Cultureinfo.InvariantCulture).toString("dd.MM.yyyy")

Hope you understand!!

8

@maciek

as per error in one of your rows the value empty

so first use an if condition to check if the value is empty r not and if not empty then convert using above expression else as it is empty nothing to convert

cheers

9

I am looping with for each row in data table and i created first assign: NumericDate = CurrentRow(“Start”).ToString , and second assign: NumericDate = DateTime.ParseExact(NumericDate.ToString, “yyyyMMdd”, System.Globalization.CultureInfo.InvariantCulture).ToString(“dd.MM.yyyy”). Process goes through without any errors but conversion does not work.

10

@maciek

After for each did you use write range workbook activity?

Regards,

11

Yes I used and in the written workbook dates are still in yyyyMMdd format

12

Hi @maciek

First you have to write it to the same datatable you are stroring it in a variable but not writing it to the datatable. Check below steps,
→ Use Read range workbook activity to read the excel and store in a datatable called DT.
→ Then use the for each row in datatable activity to iterate each row in DT.
→ Inside for each insert an If activity and give the below condition,

- Condition -> Not(String.isnullorempty(Currentrow("Start").toString) orelse String,isnullorwhitespace(Currentrow("Start").toString))

→ Inside then block insert an assign activity and give as below,

- Assign -> Currentrow("Start") = DateTime.ParseExact(Currentrow("Start").ToString, "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture).ToString("dd.MM.yyyy")

→ After for each row in datatable activity insert the Write range workbook activity to write the DT to the same excel.

Hope it helps!!

13

I guess issue might this, when ever you are opening the excel file its automatically converting the data format. Use this to disable that.

image
image1397×1111 211 KB

You also add ’ this to mark it as string. but that would need preprocessing while you use the data in later stages or applications.

14

Others have given you the code to do this, but it’s important to understand that dates do not have a format. Datetime variables store a value. You can use a format when you output them as a string. The value stored in the variable has no relation to the format, it has no format.

Think of it like a numeric variable with the value 2.14 in it. You can output that as a string as 002.14 or 2.14000 or whatever format you want, but that has no relation to the value because both of those numbers are the same value.

1 Like