Date format in Google Sheet

Hello,

I am doing web data scraping, and I am facing problems with the date format. The dates are captured as String, so I convert the date column to “DateTime” format using the “Change Column DataType” activity, and I selected the “DateTime” format listed under the “mscorlib”.

The date shows perfect when I write the datatables to Excel, but when I write the same tables to Google Sheet it writes the dat in this format “2021-03-14T21:00:00.000Z”, anyone can help with this plz? I only need the date without time, in this format dd-MM-yyyy.

Thank you.

@Anwar_Mirza
The datetime format often got changed while reading and writing to Excel/CSV/GoogleSheets, Its common problem.
Try this DateFormat.zip (19.3 KB)

Thank you Karuna, but this will only change the format to date and will not solve my main problem, I still need to keep the columns as DateTime format as I am doing operations related to dates. The strange thing is that it writes the dates correctly to Excel but not Google Sheets.

Let me know if you can help with this.

Appreciate your help.

I tried with google spreadsheets, Its working fine.
Screen shot is for your reference

Steps to be followed

  1. Gsuite Application Scope
  2. Read Range
  3. For each row
  4. Assign → String GetDate = DateTime.ParseExact(row(“Timestamp”).ToString, “dd/MM/yyyy HH:mm:ss”,System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat).ToString(“dd-MM-yyyy HH:mm:ss”)

(You need to specify both Date and Time because Google Spreadsheet DateTime column type is DateTime and spreadsheet take it in DateTime format)

  1. Assign → Your Datetime column in Datatable = GetDate
  2. Write Range.

OUTPUT

Output

Great, it worked. THANK You. :grinning:

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