Here in the above table based on Date column and Code column i need to add Finaldate values calculating from DT column,
see here in the Date column it is 7/26/2021 i have to calculate the final date by considering T+ value in DT column, if it T+3 i have to increase the date 3 times but the challenge here is i have to exclude sat only if it cucum from code column and exclude sat and sunday date if it is other than cucum…how do i achive this?
Date
T
T+1
T+2
7/26/2024
7/26/2024
7/29/2024
7/30/2024
see this is the above example, here i have excluded 27th and 28th bcz it is sat and sunday
We are using VB.net, please help me to achieve this, do i have to loop through and check using condition? according to date i have to increment checking T + values, so here i have to extract number from T + 3 right? then i have to check if the date is sat or sunday i have exclude and increment it accordingly
Do you want to programatically add the Final Date column based on your requirements (extract the number from the DT column and add that amount of days to the date in the Date column excluding Saturday if Code = “Cucum”, otherwise excluding both Saturday and Sunday?
I have built your table using a Build Data Table activity:
Create an Invoke Code activity with VBNet for the language. Add an argument to the activity as such, replacing YourTable with the name of your DataTable variable
Add the following code to the Invoke Code activity:
' Add the Final Date column to the DataTable
table.Columns.Add("Final Date", GetType(DateTime))
' Loop through each row to calculate the Final Date
For Each row As DataRow In table.Rows
Dim startDate As DateTime = Convert.ToDateTime(row("Date")).Date ' Ensure we are working only with the date part
Dim code As String = row("Code").ToString()
Dim daysToAdd As Integer = Integer.Parse(row("DT").ToString().Substring(2))
Dim finalDate As DateTime = startDate
Dim daysAdded As Integer = 0
' Calculate the final date by skipping the specified days
While daysAdded < daysToAdd
finalDate = finalDate.AddDays(1)
If code = "Cucum" Then
' Exclude Saturdays
If finalDate.DayOfWeek <> DayOfWeek.Saturday Then
daysAdded += 1
End If
Else
' Exclude Saturdays and Sundays
If finalDate.DayOfWeek <> DayOfWeek.Saturday AndAlso finalDate.DayOfWeek <> DayOfWeek.Sunday Then
daysAdded += 1
End If
End If
End While
' Set the calculated final date to the Final Date column
row("Final Date") = finalDate.Date ' Ensure only the date part is set
Next
' Display the results
For Each row As DataRow In table.Rows
Console.WriteLine($"{row("Date"):d} - {row("Code")} - {row("DT")} - {row("Final Date"):d}")
Next
If you don’t want to print the results, remove this section of code:
' Display the results
For Each row As DataRow In table.Rows
Console.WriteLine($"{row("Date"):d} - {row("Code")} - {row("DT")} - {row("Final Date"):d}")
Next
' Loop through each row to calculate the Final Date
For Each row As DataRow In table.Rows
Dim startDate As DateTime = Convert.ToDateTime(row("Date")).Date ' Ensure we are working only with the date part
Dim code As String = row("Code").ToString()
Dim daysToAdd As Integer = Integer.Parse(row("DT").ToString().Substring(2))
Dim finalDate As DateTime = startDate
Dim daysAdded As Integer = 0
' Calculate the final date by skipping the specified days
While daysAdded < daysToAdd
finalDate = finalDate.AddDays(1)
If code = "Cucum" Then
' Exclude Saturdays
If finalDate.DayOfWeek <> DayOfWeek.Saturday Then
daysAdded += 1
End If
Else
' Exclude Saturdays and Sundays
If finalDate.DayOfWeek <> DayOfWeek.Saturday AndAlso finalDate.DayOfWeek <> DayOfWeek.Sunday Then
daysAdded += 1
End If
End If
End While
' Set the calculated final date to the Final Date column
row("Final Date") = finalDate.Date ' Ensure only the date part is set
Next
' Display the results
For Each row As DataRow In table.Rows
Console.WriteLine($"{row("Date"):d} - {row("Code")} - {row("DT")} - {row("Final Date"):d}")
Next
You can do it simply with LINQ Expression, check the below steps,
→ Use the Read range workbook activity to read the excel and store in a datatable called dt.
→ Then use the assign activity to write the below expression,
- Assign -> dt = (From row In dt.AsEnumerable()
Let DateCol = row("Date").ToString()
Let Code = row("Code").ToString()
Let DTValue = row("DT").ToString()
Let AddorSubDays = System.Text.RegularExpressions.Regex.Match(DTValue.ToString,"\d+").Value
Let FinalDate = DateTime.ParseExact(DateCol, "M/d/yyyy", System.Globalization.CultureInfo.InvariantCulture).AddDays(+CInt(AddorSubDays)).ToString
Let FinalizeDate = If(CDate(FinalDate).DayOfWeek.ToString.Equals("Saturday"),CDate(FinalDate).AddDays(+2).ToString("M/d/yyyy"),(If(CDate(FinalDate).DayOfWeek.ToString.Equals("Sunday"),CDate(FinalDate).AddDays(+1).ToString("M/d/yyyy"),CDate(FinalDate).ToString("M/d/yyyy"))))
Let Note = If(Code.ToString().Trim.ToLower.Equals("cucum"),"Should exclude only Sat","Should exclude Sat And Sunday")
Select dt.Clone().Rows.add({DateCol, Code, DTValue, FinalizeDate, Note})
).CopyToDataTable()
→ Then use the Write range workbook activity to write the dt to the Same excel sheet.
Check the below workflow for better understanding, Sequence5.xaml (10.0 KB)
