Retrieve Date & Time data from Excel

Hi, what result do I get when I attempt to capture data in Excel that is formatted as Date or Time? Do I get the number of seconds from 1-1-1900, or a nicely formatted String?

If write the only date or time, you will get in date/time format if the cells are already formatted. If not for formatted it will show in OA Date value like you mentioned. If you want avoid that add a before the date or time string which you write. Then you will get the format as you want.

@devKarthikeyanR

Sry, what do you mean? If it is displayed as 26/5/2019 18:00:00, when I read the cell do I get a String of “26/5/2019 18:00:00”? Does this work even if the underlying data is formatted as Date type?

Also what is OA Date?

Buddy @DEATHFISH

Its all depending on the value in the excel buddy and i would like to give you some small examples

  1. see here i don’t have excel cell value with time and seconds, only the date like this
    image
  2. another cell i have a cel value with time and seconds in it
    image
  3. Now if read those cells i get like this in studio write line activity, see we get the date with time and seconds,
    image
  4. So if you need to get the date alone or time alone you can do likethis buddy
    out_date_value = datetimevaraible.Tostring(“dd/MM/yyyy”)
    or
    out_time_value = datetimevariable.ToString(“hh:mm:ss”)

Thats all buddy you are done.
hope this would help you and kindly revert for any queries
Cheers @DEATHFISH

While reading the cell, if check the preserve format property then you get the output as displayed in excel.

Then about OA.

https://docs.microsoft.com/en-us/dotnet/api/system.datetime.tooadate?view=netframework-4.8

Regards,
Karthikeyan Ramamoorthy

@Palaniyappan @devKarthikeyanR
So I got the date not by reading the cell, but by reading the entire table and date is just one of the many columns.

In the Excel sheet, the date is displayed as “26-May”

However, I read the table and write the result somewhere else, I get result “26/05/19 00:00:00”

How do I write the result as “26-May” ?

1 Like

Buddy @DEATHFISH

Once you get the value from excel with value like “26-May”
assign it to a variable of type string like this and you can write it as same value with this
like if we assigned the value from excel toa variable named out_date with value “26-May”
then
out_date_modified = Datetime.ParseExact(out_date.ToString,“dd-MMM”,System.Globalization.CultureInfo.InvariantCulture).ToString(“dd-MMM”)

Thats all buddy we would get the result as 26-May from excel and we can write the result anywhere
Try this and let know whether this works or not buddy @DEATHFISH
Cheers

Did that work buddy @DEATHFISH

@Palaniyappan

Now I get error

System exception.String was not recognized as a valid DateTime

1 Like

Alright buddy
i changed just now typo mistake
here you go @DEATHFISH

out_date_modified = Datetime.ParseExact(out_date.ToString,“dd-MMMM”,System.Globalization.CultureInfo.InvariantCulture).ToString(“dd-MMM”)