Excel Timestamp Formatting

Help
, ,
1

Hello,

I am currently working on an automation where I pull time data for an employee representing the number of hours and minutes worked by that employee in Excel. Unfortunately, this is not an Excel document that I have control over and can manage and therefore there are many formatting “quirks” about this data. The first of these quirks, the column for this data is actually 3 columns merged into one single column. I do not believe this to be an immediate problem as I am able to extract data from other fields in this format just fine. Secondly, instead of listing the hours and minutes in a general format, it is listed as a timestamp (hh:mm:ss AM/PM)

An example of the data as it would appear in Excel is as follows:

10:00:00 AM

This is actually representative of an employee working 10 hours, 0 minutes and 0 seconds.

When reading this cell using Excel Application Scope in UiPath Studio and writing to the console (Write Line Activity) using “.ToString” method, the result is as follows:

Input: 10:00:00 AM
Output: 0.416666666

What I would like to know is: Would there be a way to import data as a string in the format hh:mm:ss (as value from excel) and from this being able to split the data on a delimiter and continue with processing?

2

Hi @sean.finn

Try this

Cdate(row(“Date1”).ToString(“dd/MM/yyyy hh:mm:ss:tt”)

Thanks
ashwin S

3

Encountered error:

Compiler error(s) encountered processing expression
“Cdate(row(12).ToString(“dd/MM/yyyy hh:mm:ss:tt”))”.
Option Strict On disallows implicit conversions from ‘String’ to ‘Integer’.

I don’t see where the conversion to “Integer” occurs. If I am not mistaken, “CDate” converts a string to a date object rather than an integer.

Thanks,
Sean

4

Hi @sean.finn

Try this

DateTime.parseexact(row(“Date1”). ToString,“dd/MM/yyyy”, System.globalization.InvariantCulture.cultureinfo,"dd/MM/yyyy hh:mm:ss:tt)

Thanks
Ashwin.S