Hello Team,
How can we add assign based on following condition
We have this column in excel file sheet
ProjectStartDate | ProjectEndDate | Status
01-01-2024 | 06-05-2024 | HR Project
07-06-2024 | 20-08-2024 | Operation Team
Example today’s date is 1st March 2024
which is between column ProjectStartDate (01-01-2024) and ProjectEndDate (06-05-2024)
So status would be HR Project
Thanks and Regards
Hi @NISHITHA
Try this
dtOutput = (From row In dtInput.AsEnumerable()
Let startDate = DateTime.ParseExact(row.Field(Of String)(“ProjectStartDate”), “dd-MM-yyyy”, CultureInfo.InvariantCulture)
Let endDate = DateTime.ParseExact(row.Field(Of String)(“ProjectEndDate”), “dd-MM-yyyy”, CultureInfo.InvariantCulture)
Let status = If(DateTime.Now.Date >= startDate AndAlso DateTime.Now.Date <= endDate, row.Field(Of String)(“Status”), “”)
Select dtInput.Clone().Rows.Add(startDate.ToString(“dd-MM-yyyy”), endDate.ToString(“dd-MM-yyyy”), status)).CopyToDataTable()
Regards,
Hi,
Can you try the following sample?
dr = dt.AsEnumerable.Where(Function(r) CDate(r("ProjectStartDate"))<Today AndAlso CDate(r("ProjectEndDate"))>Today).FirstOrDefault
note: dr is datarow type
Sample
Sample20240304-2.zip (8.9 KB)
Regards,
@Yoichi thanks for reference
can we make 2024 (year to consider in file)dynamic
ProjectStartDate and ProjectEndDate in Excel file
Hi,Hi,
Can you elaborate? If possible, can you share specific sample for your requirement?
Regards,
OffCourse @Yoichi
Example
If this is the file
And today_date
Needs to be replaced by Date.Now.ToString(“yyyy”)
Can we update it to dynamic way
data.xlsx (8.0 KB)
Hi,
If I understand your requirement correctly, the following will work.
(it may be better to use DateTime manipulation because of possibility of leap year)
Sample
Sample20240304-2 (2).zip (10.7 KB)
Please check Sequence.xaml
Regards,
Please try the below linQ Expression,
DT.AsEnumerable.Where(Function(X) DateTime.ParseExact((X("ProjectStartDate").ToString.Replace("today_date",DateTime.Now.ToString("yyyy"))).ToString,"M/d/yyyy",System.Globalization.CultureInfo.InvariantCulture)<=DateTime.Now AndAlso DateTime.ParseExact((X("ProjectEndDate").ToString.Replace("today_date",DateTime.Now.ToString("yyyy"))).ToString,"M/d/yyyy",System.Globalization.CultureInfo.InvariantCulture)>=DateTime.Now).Select(Function(X) X("Status").ToString).FirstOrDefault
I have given the year dynamically, it will change year based on present year.
Hope it helps!!
Thanks @lrtetala for Sharing
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.
