Retrieve Date & Time data from Excel

Help
1

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?

2

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.

3

@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?

4

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

5

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

1 Like
6

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

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

8

Did that work buddy @DEATHFISH

9

@Palaniyappan

Now I get error

System exception.String was not recognized as a valid DateTime

1 Like
10

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”)

11

Sorry to revive this - but I’m now having a similar issue which has never happened before. I am also using the snippet to change the date to JUNE etc or whatever is relevant (strVar2), however my issue is occurring the step before.

I get the System exception.String was not recognized as a valid DateTime when trying to read the date call in excel (datVar1), which is date formatted. I have written as both UK and US versions, both times I get the error, so it never proceeds to the step of changing to store the month as another variable.

Any ideas why Read Cell activity is throwing an error reading a date format in a date cell if output (datVar1) is DateTime format?

Thanks

12

Self reply - I entered the date wrong, so…this one’s on me and a reminder to proof read. September only has 30 days :stuck_out_tongue:

13

Can you please help me …my excel has “7/30/20 12:30 AM” …how to bring it to studio as dateTime object so i could use it for sending meeting request .