Check if date column in excel is null and compare it with current date and write Y/N to a new column

Hello All,
i have a condition to check-
if Start Date is not null
and End Date is not null and End Date >= today then “Y”/“N”.
in the attached excel i have input(3 columns) and in output i am adding one more column to output the result Y/N.
i am not sure how to do this,inside each row i have taken an if condition:
(NOT String.IsNullOrEmpty(row(“Start Date”).ToString.Trim) AND(NOT String.IsNullOrEmpty(row(“End Date”).ToString.Trim)) AND(Datetime.Parseexact(row(“End Date”).ToString.Substring(0,10),“dd-mmm-yy”,System.Globalization.CultureInfo.InvariantCulture).ToString(“dd/MM/yyyy”).Equals(now.ToString(“dd/MM/yyyy”))))=True
then assign in true ='Y" else “N”.
but i am getting an error -If: Index and length must refer to a location within the string.
Parameter name: length.
Can someone please give me a solution for the attached example file with similar scenario.DATE Eg.xlsx (16.4 KB)

Hi,

  1. used read range
  2. use for each row activity
    3.in For each used get row item
    4.used if condition in which…your date variable=system.ToDateTime.now.tostring("dd-mm-yy)…
    5 in If condition used assing in that what you want to write …
1 Like

@astha_gupta That error is caused because of SubString Method, Indicating there are lesser characters in the String than the number you have provided

If there are empty values in Date Columns you would need to handle that condition as well

1 Like

since now i gave .Substring(0.9) for date coming in column like 19-May-20 but same error is coming again.
in if condition i have put
(NOT String.IsNullOrEmpty(row(“Start Date”).ToString.Trim) AND(NOT String.IsNullOrEmpty(row(“End Date”).ToString.Trim)) AND(Datetime.Parseexact(row(“End Date”).ToString.Substring(0,9),“dd-mmm-yy”,System.Globalization.CultureInfo.InvariantCulture).ToString(“dd/MM/yyyy”).Equals(now.ToString(“dd/MM/yyyy”))))=True

then assigned row("Serving ")=“Y” else row("Serving ")=“N” .
can you please share the solution of the example file(similar scenario)?i am not able to debug it.DATE Eg.xlsx (16.4 KB)

@astha_gupta Maybe use AndAlso instead of AND

1 Like

perfect <3!It worked thanks a lot:)

1 Like

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