Format or value of excel data changes

i am reading the excel and storing in the database. One column name is Datetime with date time format data but when i am reading it the value changes. For example:
If the cell value is 7/8/20 1:18:58 AM , the value in the datatable is 44026.1733680556.

Please help?

Hi @harshit_gupta

For converting Type you can refer below thread :-

Mark as solution and like it if this helps you :slight_smile:

Happy Automation :raised_hands:

Best Regards
Er Pratik Wavhal :robot::man_technologist:t4: :computer:

1 Like

Two options :

  1. You can change the spreadsheet column to the date format or text format so that you can read it as a string/generic value.
  2. while you iterate, you can use the β€œFormat value” activity to get your output as a desired format. In your case, you can change the mm/DD/yyyy hh:mm:ss value as per ur requirement.

Try it out and let us know if it works.

in case of you want to manually get control over it before inserting it into the datatabe the double value can be parsed into a datetime and e.g. reformated as a string. Have a look here:

Bringing it back to another Timezone:

TimeZoneInfo.ConvertTimeBySystemTimeZoneId(DateTime.FromOADate(CDbl(β€œ44026.1733680556”)),TimeZoneInfo.Local.Id, β€œIndia Standard Time”)

1 Like