How to subtract days based on date encoded and Effectivity Date

[Vincent_Nuestro]

Hi I want to get all record based on “Date encoded” and Effectivity date Column on my excel.

Condition:
if “Date Encoded” is less than 14days of “Effectivity Date”
Write in Excel

If Date encoded is greater than or Equal 14Days of effectivity date.
Write in excel.

Please see example delow

thank you in advance
-Vincent

[geetishree.rao]

Dear Vincent,

Could you confirm if the Date Encoded is already present in the excel or is it the Current date.

And please confirm the logic for the same,
as per your logic Date Encoded can be any day >=,<14(all days fall in this category))
Write In Excel =if DayDiff(Date Encoded,EffectivityDate)>=14 then Date Encoded
Write In excel =if DayDiff(Date Encoded,EffectivityDate) < 14 then Date Encoded

Thanks,
Geetishree Rao

[Vincent_Nuestro]

hi @geetishree.rao the date encoded is already present in the excel.

Write In Excel =if “Date Encoded” >= 14 days of “Effectivity Date”
Write In excel =if “Date Encoded” < 14 Days of “Effectivity Date”

Regards
-Vincent

[Yoichi]

Hi,

If we can use excel formula, the following might help you.

"=IF(F"+(index+2).ToString+"-G"+(index+2).ToString+"<14,""<14 days"","">=14 days"")"

Sample20211012-2.zip (8.9 KB)

Regards,

[Vincent_Nuestro]

hi @Yoichi

is there any other way to separate the condition like using “IF” activity?
because i have more condition statement after determining if the date encoded is < 14 days of Effectivity date or date Encode is >= 14 Days Effectivity Date

Something like this

[Yoichi]

Hi,

@Vincent_Nuestro

is there any other way to separate the condition like using “IF” activity?

Hope the following helps you.

(DateTime.Parse(row("Effectivity Date").ToString)-DateTime.Parse(row("Date Encoded").ToString)).TotalDays<14

Sample20211012-2v2.zip (9.0 KB)

Regards,

[geetishree.rao]

Dear Vincent,

Please follow the below steps:
1.Read the excel
2.Build a datatable with same structure as the above excel dt.Clone
3.Loop through the extracted data, and put the condition to check the diff between the 2 columns to satisfy:

if DateDiff(DateInterval.Day,Date.ParseExact(“MM/dd/yyyy”,CurrentRow.Item(“Date Encoded”).ToString.Trim,System.Globalization.CultureInfo.InvariantCulture),Date.ParseExact(“MM/dd/yyyy”,CurrentRow.Item(“Effectivity Date”).ToString.Trim,System.Globalization.CultureInfo.InvariantCulture))

< or >= 14 then pass the row to add data row activity to the datatable built in step2

4.At the end write range the datatable of step 2 to the excel

Thanks,
Geetishree Rao

[Vincent_Nuestro]

hi @Yoichi

It works, thanks for the help