Calulating whether In Time and Out time of employees is as per rules

RPA Discussions
,
1

I wish to know whether employee’s first punch time is between 11 am -11.30am and last punch time is after 3 pm on a particular day.

There can be multiple punch times on a particular day.

If on that day employee does not meet this condition then I want a new column where absent is maintained against that employee.

Below is data…

|Employee Name|Employee Email|Date|Time|

|ABC|abc@himedialabs.com|01/11/2021|11:15 AM|
|ABC|abc@himedialabs.com|01/11/2021|12:45 PM|
|ABC|abc@himedialabs.com|01/11/2021|12:48 PM|
|ABC|abc@himedialabs.com|02/11/2021|11:52 AM|
|ABC|abc@himedialabs.com|02/11/2021|12:38 PM|
|ABC|abc@himedialabs.com|03/11/2021|10:56 AM|
|ABC|abc@himedialabs.com|03/11/2021|11:48 AM|
|ABC|abc@himedialabs.com|08/11/2021|11:33 AM|
|ABC|abc@himedialabs.com|08/11/2021|12:08 PM|
|ABC|abc@himedialabs.com|08/11/2021|12:57 PM|
|ABC|abc@himedialabs.com|11/11/2021|11:39 AM|
|ABC|abc@himedialabs.com|11/11/2021|2:10 PM|

Thanks.

Pratik

2

HI @Pratik2pP

Try this condition for 11Am-11:30 AM

 
DateTime.ParseExact(Row("Time").tostring,"hh:mm tt",System.Globalization.CultureInfo.InvariantCulture)=DateTime.ParseExact("11:00 AM","hh:mm tt",System.Globalization.CultureInfo.InvariantCulture) or DateTime.ParseExact(Row("Time").tostring,"hh:mm tt",System.Globalization.CultureInfo.InvariantCulture)<DateTime.ParseExact("11:30 AM","hh:mm tt",System.Globalization.CultureInfo.InvariantCulture)

After 3pm


DateTime.ParseExact(Row("Time").tostring,"hh:mm tt",System.Globalization.CultureInfo.InvariantCulture)>DateTime.ParseExact("03:00 PM","hh:mm tt",System.Globalization.CultureInfo.InvariantCulture)

Hope this helps

Regards
Sudharsan

3

Hi,

Hope the following sample helps you.

img20211231-4
img20211231-4681×689 21.7 KB 

dtResult = dt.AsEnumerable.GroupBy(Function(r) Tuple.Create(r("Employee Name").ToString,r("Employee Email").ToString,r("Date").ToString)).Select(Function(g) dtResult.Clone.LoadDataRow({g.Key.Item1,g.key.Item2,g.Key.Item3,(not (DateTime.Parse(g.First().item("Time").ToString).TimeOfDay>=New  TimeSpan(11,0,0) AndAlso DateTime.Parse(g.First().item("Time").ToString).TimeOfDay<=New  TimeSpan(11,30,0) AndAlso DateTime.Parse(g.Last().item("Time").ToString).TimeOfDay>=New  TimeSpan(15,0,0))).ToString},False)).CopyToDataTable

Sample20211231-2.zip (3.3 KB)

Note: This workflow assumes original data is sorted by date and time.

Regards,

4

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