Format or value of excel data changes

Help
1

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?

2

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
3

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.

4

@harshit_gupta
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:

grafik
grafik872Ă—102 3.36 KB

Bringing it back to another Timezone:

grafik
grafik1317Ă—47 3.04 KB

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

1 Like