8 hours worktime

[fa2020]

Hi everybody,
I have thousands of rows (about 37000 rows) in a sheet which contains lots of information. Some of those important information which are used for creating a report or chart are personnel names, their work time, and the corresponding date they have done their tasks. Because of some mistakes taken by the users who fill these information (using Network), some personnel work-time considering their date, exceeds 8 hours. So, I need to write a code in order to prevent any surplus work-time put into the table. For example, Alex worked for 8 hours (cumulative) in 21/05/2020, but the user who imported his information, has written 11 hours (cumulative) for that specific date and person. I want to write a code to prevent any work-time more than 8 hours for a day to be calculated. For better understanding, I created a sample datasheet. Please, guide me about this problem. How should I write a code in Excel or PowerPivot (DAX Code) to solve this issue in my large data sheet?

[JBeaucaire]

This quick little macro will make a summary of data it finds in columns A:C and put the summary in G:I with a max of 8 hours per day summarized.

Please Login or Register  to view this content.

[popipipo]

You can also use datavalidation in column B for this

=SUMIFS($B$2:B2,$A$2:A2,A2,$C$2:C2,C2)<=8

[fa2020]

This quick little macro will make a summary of data it finds in columns A:C and put the summary in G:I with a max of 8 hours per day summarized.

Please Login or Register  to view this content.

Nice solution. Thanks.

[fa2020]

You can also use datavalidation in column B for this

=SUMIFS($B$2:B2,$A$2:A2,A2,$C$2:C2,C2)<=8

Thanks. It was helpful.

[fa2020]

This quick little macro will make a summary of data it finds in columns A:C and put the summary in G:I with a max of 8 hours per day summarized.

Please Login or Register  to view this content.

Can you please add some explanation for each part of your code? I want to develop it to my own situation but I'm new to VBA.

[JBeaucaire]

Please Login or Register  to view this content.