You can use the LINQ Expressions to get the required data, Follow the below steps,
→ Use the Read range workbook activity to read the excel and store in a datatable called Input_dt.
→ Then use the assign activity and write the below LINQ Expression,
- Assign -> Output_dt = (From row In Input_dt.AsEnumerable()
Let TerminationDate = CDate(row("Termination Date"))
Let Bool_workperiods = If(row("Logon").ToString().Equals("not in use"), False, If(CDate(row("Logon")) > TerminationDate, True, False))
Let weekperiods = If(Bool_workperiods, TerminationDate.AddDays(-(TerminationDate.DayOfWeek + 6) Mod 7).ToString("MM/dd/yyyy") & "-" & TerminationDate.ToString("MM/dd/yyyy") & ", " & CDate(row("Logon")).AddDays(-(CDate(row("Logon")).DayOfWeek + 6) Mod 7).ToString("MM/dd/yyyy") & "-" & CDate(row("Logon")).ToString("MM/dd/yyyy"), "")
Let Status = If((Bool_workperiods.equals(False)), "To be Deleted", "LogRequired")
Select Input_dt.Clone.Rows.Add({row("UserID").ToString(), row("Logon"), TerminationDate.ToString("MM/dd/yyyy"), weekperiods, Status})
).CopyToDataTable()
→ Then use the write range workbook activity to write the Output_dt to the Excel file.
Check the below workflow for better understanding, Sequence2.xaml (13.8 KB)
And would also recommend check deeply if a LINQ approach for column value updating is the approach which will fit to your situation (skill set, maintenance tasks…)
With the division of the different subtasks, row looping, the filtering and general updating should be well known by you and be fine.
So, we would focus on the week period calculations and suggest to clear the details.
is Termination Date always a Sunday?
Line 4: 15-23 July, is this span not split into weeks and how to handle remaining fractions?
User2 is Termination Date & Logon date comes under week27 …but getting different week period ( when the termination & logon date falls between only one week then week period contains only that week numbers period)
User3 dates comes under week 28 & 29… getting correct week period for week 28… but is different for week 29
Also is it possible to add backward slash before every forward slash in the week period?
Attached excel sheet with input and expected output: Output1.xlsx (9.5 KB)
Here is the reason why we need to week period in this format
Bot needs to click required week periods for the terminated users and get logs in SAP
User 2 is termination date on July 1 and logon date is July 6 so the expected week period is 07/01/2024-07/07/2024 ( week number 27 )
User 3 is termination date on July 14 and logon date is July 20 , so the expected week period is 07/08/2024-07/14/2024, 07/15/2024-07/21/2024 ( week number 28 & 29)
User2 is Termination Date & Logon date comes under week27 …but getting different week period ( when the termination & logon date falls between only one week then week period contains only that week numbers period)
User3 dates comes under week 28 & 29… getting correct week period for week 28… but is different for week 29
Also is it possible to add backward slash before every forward slash in the week period?
Attached excel sheet with input and expected output: Output1.xlsx (9.5 KB)
Here is the reason why we need to week period in this format
Bot needs to click required week periods for the terminated users and get logs in SAP
User 2 is termination date on July 1 and logon date is July 6 so the expected week period is 07/01/2024-07/07/2024 ( week number 27 )
User 3 is termination date on July 14 and logon date is July 20 , so the expected week period is 07/08/2024-07/14/2024, 07/15/2024-07/21/2024 ( week number 28 & 29)
