How to calculate Missing Employee Entries

[Amrit_Sahoo]

Hi Team,

Anyone can help me with to calculate the employee entries from a time sheet data.

Time sheet table:

Employee Name	Employee ID	Date	Hours
A	1	02-08-2021	8
B	2	02-08-2021	8
A	1	03-08-2021	8
B	2	03-08-2021	8
A	1	04-08-2021	8
A	1	05-08-2021	8
B	2	05-08-2021	8

In this table Employee B has missed to enter the hours for 4th August. So how do I calculate the Employee Name & id and missing date from the table.

My input files are

1. Timesheet Dump as like the table above

2. List of the employees.

Please help with the logic.

Thanks in advance.

Regards,

Amrit

[atcodedog05 · Accepted solution]

Hi @Amrit_Sahoo 

Here is how you can do it.

Workflow:

1. Using summarize tool create list of unique dates.

2. Using append tool create combination of employees for each dates.

3. Using join multiple to do an outer join on Employee Name, Employee ID & Date. This adds the missing rows.

4. Using formula tool to replace nulls (new entries) with 8.

5. Using sort tool sort by date then by employee id.

Hope this helps : )

[Amrit_Sahoo]

Thanks a ton.

[atcodedog05]

Happy to help : ) @Amrit_Sahoo 

Cheers and have a nice day!