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.
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?
Its all depending on the value in the excel buddy and i would like to give you some small examples
- see here i don’t have excel cell value with time and seconds, only the date like this
- another cell i have a cel value with time and seconds in it
- Now if read those cells i get like this in studio write line activity, see we get the date with time and seconds,
- 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”)
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
While reading the cell, if check the preserve format property then you get the output as displayed in excel.
Then about OA.
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” ?
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”
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
Did that work buddy @DEATHFISH
Now I get error
System exception.String was not recognized as a valid DateTime
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”)
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?
Self reply - I entered the date wrong, so…this one’s on me and a reminder to proof read. September only has 30 days