Compare two Columns in Excel and check if first columns dates are more than 30 days for the second column

Help Studio
, ,
1

I need to compare two date columns in Data table and and check if the first column date is less than 30 days than second column and adding the data row saying Yes or No for 30 days.
Please can someone help for this
#excel #datatable

2

Hi @Kunal_Jain

You can try this approach

image

Note: Please make sure that the two date columns are in date format. If not please convert them to date before comparison

Refer this xaml to have a clarity

LessThan30Days.xaml (8.9 KB)

3

I am sharing the sheet with you
I am getting error while using the code.
Testing Report.xlsx (9.7 KB)

4

Hi @Kunal_Jain

There are some empty values in the first date column.

What is to be done in this scenario?

5

If it contains empty columns than we just need to leave them as No in the Remark column

6

@Kunal_Jain

Try this xaml

LessThan30Days.xaml (12.2 KB)

7

can we have a LinQuery for the same
And also if firstDate is Empty we do not have to write anything for that.
Can we have that ?

8

Hi @Kunal_Jain

I have implemented it using the LINQ, please check

(	
	From row In dt_Data.AsEnumerable()
	Let fd = row("Adjusted Due Date").ToString.Trim
	Let sd = row("WO Start Date").ToString.Trim
	Let c1 = Not String.IsNullOrEmpty(fd)
	Let c2 = Not String.IsNullOrEmpty(sd)
	Let remark = If(c1 AndAlso c2, If((CDate(sd).Date -CDate(fd).Date).Days < 30, "Yes", "No"), "")
	Select dt_Result.Rows.Add({row("Adjusted Due Date"), row("WO Start Date"), remark})
).CopyToDataTable

Refer the xaml

LessThan30Days.xaml (11.6 KB)

9

Here in this code only problem is with negative values,
Here check this out
03-07-2022 15-11-2021 Yes -230
Here for this value it must be a No
But it is giving a Yes

10

@Kunal_Jain

Please refer the modified LINQ

(	
	From row In dt_Data.AsEnumerable()
	Let fd = row("Adjusted Due Date").ToString.Trim
	Let sd = row("WO Start Date").ToString.Trim
	Let c1 = Not String.IsNullOrEmpty(fd)
	Let c2 = Not String.IsNullOrEmpty(sd)
	Let remark = If(c1 AndAlso c2, If( (CDate(sd).Date -CDate(fd).Date).Days < 30 And (CDate(sd).Date -CDate(fd).Date).Days >= 0, "Yes", "No"), "")
	Select dt_Result.Rows.Add({row("Adjusted Due Date"), row("WO Start Date"), remark})
).CopyToDataTable

Xaml

LessThan30Days.xaml (11.6 KB)

11

This is the solution
Cheers!!

12

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