Rolling Sum Based on Weekday

MD2050

Good Evening -

I am trying to sum employee hours based on the what work day we are generating the daily report.
For example if there is an employee X who worked on following days of the week :-

Sun=9 Hours

Mon = 8 Hours ,

Tue= 7 Hours ,

Wed= 8 Hours

Thu=6 Hours,

Fri=4 Hours

Sat= 10 Hours

I am working on a report which should club the hours based on what day the report comes out , meaning if the report is generated on Monday then it should show 9 hours , if the report is generated on Tue then it should show 17 hours ..... if the report is generated on Sat then it should show 42 hours .(This will be a daily report )

I am trying to sum based on the work day .

I have attempted the same in the attached workflow , i not successful so far.

Will really appreciate any help.

please find in attached workflow and sample data.

Thank you very much.

week_data.yxmd

test_data.xlsx

Preparation

Accepted answers

unknown

Answer attached.

week_data.yxmd

unknown

Group By Employee and Sum Hours and Count Days. Then for expected, take (5 - Count of Days)*8 Hours + Sum Hours in the previous step.

All comments

unknown

Answer attached.

week_data.yxmd

vishwa_0308

In my opinion you can simply use the Running Total tool to get the cumulative sum as per the records arrives in your daily report.

MD2050

Hello @Inactive User-

Thank you very much for your help , it really helped alot.

I have one follow- up question- if i have to project how much total hours an employee will work by the end of the week how will i be able to code that given the probability that an employee can start on Sunday as 1st day of work or can start on Monday as 1st day of work for the week.

For example : if employee X started on Sun worked 8 hours , worked on Monday for 5 hours then the report which comes out on Tuesday should show 13 hours worked so far and 37 expected hours to work by end of Thursday (considering 8 hrs per day and 5 work days a week)

now if another employee Y started on Mon worked 8 hours , Tuesday 6 hours then the report which comes out on Wed should show 14 hours worked so far and 38 expected hours to work by end of Friday.

The report will have mix employees who will start on Sunday or Monday , i am trying to work on the logic on how to calculate the total expected hours worked by an employee by the end of the work week.

Thank you very much for your help.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703