Extract date from string and apply condition

Help Studio
, ,
1

Hi Iamable to extract the dates from the string ,dates are in different formats like–01042024,08-12-23,10/12/2024, here i want apply a condition , if my extract should be in between 30-45 days , if it is not in between that days than it should delete all rows from the excel, i apply the condition for this–If(CDate(ExtractedDate).AddDays(30) > DateTime.Now AndAlso CDate(ExtractedDate).AddDays(-45) < DateTime.Now, dtFiltered, New System.Data.DataTable())----- but it show me error when it receive a date like this–01042024, suggest me a good condition or correct this if my condition is wrong

Thanks

2

Hi @Arvind1

Can you try the below expression,

If(DateTime.Parseexact(ExtractedDate.tostring, {"MM-dd-yyyy","MM/dd/yyyy","MMddyyyy"},System.Globalization.Cultureinfo.Invariantculture, System.Globalization.Datetimestyles.None).AddDays(30) > DateTime.Now AndAlso DateTime.Parseexact(ExtractedDate.tostring, {"MM-dd-yyyy","MM/dd/yyyy","MMddyyyy"},System.Globalization.Cultureinfo.Invariantculture, System.Globalization.Datetimestyles.None).AddDays(-45) < DateTime.Now, dtFiltered, New System.Data.DataTable())

Hope it helps!!

3

Hi @mkankatala i receive the error by this-- Assign: String ‘’ was not recognized as a valid DateTime.---- this is due to this date like 01042023
Help here how solve it

4

@Arvind1

Dim dateFormats As String() = {"ddMMyyyy", "MM-dd-yy", "MM/dd/yyyy"}
Dim today As DateTime = DateTime.Now

dtFiltered = (From row In dt.AsEnumerable()
                               Let dateStr = row("ExtractedDate").ToString()
                               Let parsedDate = (Function()
                                                     For Each format As String In dateFormats
                                                         Dim result As DateTime
                                                         If DateTime.TryParseExact(dateStr, format, System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None, result) Then
                                                             Return result
                                                         End If
                                                     Next
                                                     Return DateTime.MinValue
                                                 End Function).Invoke()
                               Where parsedDate <> DateTime.MinValue AndAlso parsedDate.AddDays(30) > today AndAlso parsedDate.AddDays(-45) < today
                               Select row).CopyToDataTable()

Write this in invoke code

1 Like
5

Hi @rlgandu
i receive an error in this date format 061424
how i can handle this

6

Could you give me one confirmation… @Arvind1

01042023 is in MMddyyyy or ddMMyyyy format. If you have any other formats let me know according to that I’ll change the expression.

7

@Arvind1

{MMddyy}

Add this format also

Whatever formats you have that formats will be added to this list Dim dateFormats As String() = {"ddMMyyyy", "MM-dd-yy", "MM/dd/yyyy"}

1 Like
8

MMddyy format like061424

9

Okay @Arvind1

Try the below one,

If(DateTime.Parseexact(ExtractedDate.tostring, {"MM-dd-yyyy","MM/dd/yyyy","MMddyyyy","MMddyy"},System.Globalization.Cultureinfo.Invariantculture, System.Globalization.Datetimestyles.None).AddDays(30) > DateTime.Now AndAlso DateTime.Parseexact(ExtractedDate.tostring, {"MM-dd-yyyy","MM/dd/yyyy","MMddyyyy","MMddyy"},System.Globalization.Cultureinfo.Invariantculture, System.Globalization.Datetimestyles.None).AddDays(-45) < DateTime.Now, dtFiltered, New System.Data.DataTable())

I have changed the expression according to the requirement.

If you have any other type of date formats then add in between the curly braces like this in the above expression,

{"MM-dd-yyyy","MM/dd/yyyy","MMddyyyy","MMddyy"}

Hope it helps!!

1 Like
10

Hi @Arvind1

try below condtion in if should work fine

DateTime.ParseExact(ExtractedDate.ToString, {"ddMMyyyy", "dd-MM-yy","dd/MM/yyyy","MMddyy"}, System.Globalization.CultureInfo.InvariantCulture,  System.Globalization.DateTimeStyles.None).AddDays(30) > DateTime.now AndAlso  DateTime.ParseExact(ExtractedDate.ToString, {"ddMMyyyy", "dd-MM-yy","dd/MM/yyyy","MMddyy"}, System.Globalization.CultureInfo.InvariantCulture,  System.Globalization.DateTimeStyles.None).AddDays(-45) <  datetime.Now

Four types of date format i’ve added

{"ddMMyyyy", "dd-MM-yy","dd/MM/yyyy","MMddyy"}
11

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.