Need some help again!!!
Hi ,
I want to display as WO for weekoff which are rotational for the employees.
5 days a week and for 8 hours , total is 40 hours
attached input file
1 Like
Hey @shirin.sebatina
You mean the empty cells we can see in the image like Tuesday and Wednesday
Is that right ?
Thanks
#nK
yes,
But it need not be on those days itself , days may get changed
1 Like
Yes that’s fine. But just my query is how to identify that two day rotational off…
Is it the 2 consecutive empty cells with which we need to identify ?
Thanks
#nK
@shirin.sebatina Could you please explain your query in more detail?
Hi,
1st have to check for 5 days if the hour is 8 hr , so for a week it will be 40 hrs
so
check if hours is more than 40 and if the next 2 consecutive cells are blank then have to write as “WO”
1 Like
Hey @shirin.sebatina
Kindly find this below…
nmnithinkrishna_FindRotationalWeekOff.zip (8.6 KB)
Hope this helps you.
Thanks
#nK
Hi @shirin.sebatina ,
Should the condition be Greater than or Equal to 40 ?
Hi ,
yes @supermanPunch
Hi @shirin.sebatina ,
Check the workflow Below :
Find_RotationalWeekOff.zip (10.2 KB)
The method I have used is one such way that we can implement this. There may be many other efficient methods to be used.
Firstly, We Create the Table of index findings which match our conditions.
Then we can use the below Linq to fetch the row and Column Indices. The Column Values which do not meet our conditions gets a -1 value as their Index.
(From r In DT
From a In Enumerable.Range(0,CInt(DT.Columns.Count-5))
Let matches = r.ItemArray.Skip(a).Take(5).Where(Function(y)Regex.IsMatch(y.ToString,"\d+h\s\d+m")).ToArray
Let sum =If(matches.Count=5 AndAlso r.ItemArray.Count>(a+5+1) AndAlso ((String.IsNullOrWhiteSpace(r(a+5).ToString) AndAlso String.IsNullOrWhiteSpace(r(a+5+1).ToString))),matches.Sum(Function(s)DateTime.ParseExact(Regex.Match(s.ToString,"(\d+)h\s(\d+)m").Groups(1).ToString+":"+Regex.Match(s.ToString,"(\d+)h\s(\d+)m").Groups(2).ToString,"H:m",System.Globalization.CultureInfo.InvariantCulture).TimeOfDay.TotalHours),0)
Let index = If(CDbl(sum)>=40,a+5,-1)
Select RowColumnIndicesDT.Rows.Add({DT.Rows.IndexOf(r),index})).CopyToDatatable
As we would require to Convert the "8h 0m" or other time values to TimeSpan and Calculate the Total Sum, we do it in an inline method of if.
After we receive the table index findings, we can remove the rows which has -1 value in column 2.
Next, we can Iterate over the remaining values in the Datatable using For Each Row and Update the Actual Datatable like below :
Let us know if you are still facing issues with this.
2 Likes
Hi ,
There are 5 working days in week , check if they have worked 40 hours in a week irrespective of days.
If they have covered 40 hours in 3 days then next 2 days should be considered as “PL” and next two days as usually “WO”
Considering the 1st row
Here employee has worked for more than 40 hours in 3 days (Thursday, Friday, Saturday) next 2 days (Sunday , Monday ) should be filled as “PL”
next two days(Tuesday, Wednesday) as “WO”
Hi @shirin.sebatina ,
Could you provide the Sample data filled with more values such that we can identiy what is to be done on the Logic part.
The Question here is whether the Values need to be Grouped in Weeks and for that Group do we need to Check if it meets the conditions and perform the Updation or do we need to Group each 5 values check if it meets the conditions and Perform the Updation.
Grouping By Week Method :
Grouping by 5 Days each :
Let us know your response on these methods and maybe we need to revise the solution if it is Group By Week.
We have to group by weeks
Hi @shirin.sebatina ,
Take a Look at the Revised Solution below :
Find_RotationalWeekOff.zip (10.5 KB)
Firstly, We would need to know the Start Column from where the Grouping of weeks need to be done. For this, we can use the Column Previous to the Start of the Date Column as the Indicator. In your case the Column “Comments”.
We find the index of the Comments Column and thereby finding the Next Column Index which is the Column from where the Grouping of weeks Start.
Next we Prepare the Grouped Days Array for the Linq Skip and Take.
The Array Elements :
The Modified Row Column Index Datatable :
The Linq Query modified to return the indices with the value (PL or WO) to be Updated.
(From r In DT
From a In ColumnsGroupedByWeek
Let matchs = r.ItemArray.Skip(a).Take(5).Where(Function(y)Regex.IsMatch(y.ToString,"\d+h\s\d+m")).ToArray
Let sum =If(matchs.Count<=5 AndAlso matchs.Count>0,matchs.Sum(Function(s)DateTime.ParseExact(Regex.Match(s.ToString,"(\d+)h\s(\d+)m").Groups(1).ToString+":"+Regex.Match(s.ToString,"(\d+)h\s(\d+)m").Groups(2).ToString,"H:m",System.Globalization.CultureInfo.InvariantCulture).TimeOfDay.TotalHours),0)
Let diff = If(matchs.Count=5 OrElse matchs.Count=0, 0,5-matchs.Count)
Let PL_Indices = If(CDbl(sum)>=40 AndAlso matchs.Count>0 AndAlso matchs.Count<5,String.Join(",",Enumerable.Range(a+matchs.Count,diff).Select(Function(s)If(DT.Columns.Count>s,s,-1)).ToArray),"-1")
Let WL_Indices = If(CDbl(sum)>=40 AndAlso matchs.Count>0,String.Join(",",{a+5,a+6}.Select(Function(s)If(DT.Columns.Count>s,s,-1)).ToArray),"-1")
From b In PL_Indices.Concat(WL_Indices).Where(Function(s)Not(CInt(s.ToString)=-1)).ToArray
Select RowColumnIndicesDT.Rows.Add({DT.Rows.IndexOf(r),If(PL_Indices.Contains(b),"PL","WL"),b})).ToArray
The Data retrieved is in the below manner :
Next we Update the Values of the datatable by using the Indices present in the Retrieved datatable.
Let us know if the solution works for all the cases. If not, Specify the exact example here and we can take a look at it.
Also, Please provide the total requirements at first hand (Can Elaborate it by Points), as it will be a difficult task to modify/revise the solution at a Later Stage.
Thankyou for your reply!!
Appreciate it…
But for this input i am finding results.
Rotational Input.xlsx (2.1 MB)
Do you get an error when using this Input Sheet ?