My Requirement is I have an excel column names “Date of Approval” , “Start Date” , “Cessation Date”
1.“Date of Approval” consists of dates of format “dd-MMM-yyyy” , and also texts like “approved” , “rejected”
2.First i should check if “Date of Approval” column contains dates or approved ----- which logic can i use for this?
3.Then check “End Date” whether the date is expired or not – how to compare dates Note Dates can be of different format how can i first verify the format
Thanks in Advance
As per my understanding on your post i am sharing my thoughts. Could you try the steps below. thanks.
I assume you already using for each row loop to get the data from excel columns row by row.
Since we don’t know the format of dates better we have to use the below statements to initialize via assign statement to get correct date format.
System.Globalization.CultureInfo.CurrentCulture = New CultureInfo(“es-ES”)
Use if condition → Currentrow(“Date of Approval”).tostring.Contains(“rejected”)
then → keep log message saying that is rejected.
Else condition → use one more if condtion → Currentrow(“Date of Approval”).tostring.equals(“approved”)
then → keep log message saying that it is having only text approved
else → i assume that it should come with only date with some date format but i assume it should come with some valid date format. here we can use cdate function to convert date format.
cdate(Currentrow(“Date of Approval”).tostring) > cdate(Currentrow(“End Date”).tostring) . it means it got expired.
i hope the above logic might helpful for your requirement. thanks.
After the Check of "Date Of Approval" Column, that is if the value is not a text such as "Approved" or "rejected".
We can then take a look on the date formats that are present in the Column using a Write Line activity and then Collect the Date formats presented.
Then you can use the Date formats to convert it to the DateTime and then in the required string format using the below Expression :
DateTime.ParseExact(row("Date of Approval").ToString,{"MM/dd/yyyy HH:mm:ss","dd-MMM-yyyy"},System.Globalization.CultureInfo.InvariantCulture,DateTimeStyles.None)
If multiple formats are found, you can add the format separated by a Comma.
You can this linq to get new dt which has only date format
(From row In dt_input.AsEnumerable Where not (row(“Date of Approval”).ToString.ToLower.Trim.Equals(“approved”) or row(“Date of Approval”).ToString.ToLower.Trim.Equals(“rejected”) )Select row).CopyToDataTable