Calculating lost hours per week

[gd6noob]

Hi there,

I have a worksheet that calculates lost hours for employees.
Basically it cells AE to CD are weeks 1 to 52 and it looks at the start and end date and spits the numbers according to the weeks for lost hours.

The reference to the Do Not Delete tab is

The issue is that when there are lots of entries, this slows the sheet down a lot. Is there a better more efficient way to count this?


What is more efficient way of coding this?

Thanks in advanced.

 

[jasonb75]

That will probably make it easier, will take a look later.

 

[jasonb75]

Trying to do what you wanted without the week columns wasn't working as well as I hoped. I've changed the week formulas so that they work with dates that start in the previous year or end in the next. I've also added in columns for week 53 as you had missed those.

I've changed some of the test dates and it works as I believe is correct.

Tracker clean version v3.xlsx

Shared with Dropbox

www.dropbox.com

 

[gd6noob]

Yes this is working... Too bad the other way wasn't working. This is perfect thank you very much.
I just need to be careful with this as the week columns will includes hours from different years, so the data will be skewed a bit.

Thaank you for all your help, really appreciate it.

 

[jasonb75]

I just need to be careful with this as the week columns will includes hours from different years, so the data will be skewed a bit.

That was my bad, I checked the dates with the week numbers but forgot to check if it was the correct year.

I'm not uploading the file again, attaching a file with a solution already in place goes against the forum rules. Given the volume and complexity of the earlier changes, I felt that it was a necessary evil, but this time it's just a single formula change.

This formula needs to go into AE3, then you will need to drag it right and down to fill the rest of the week columns. Note that you need to fill down manually, when you drag right in a table the existing rows below do not update automatically. Once the existing formulas have been filled correctly any new rows will be added automatically as before.

=IF(N($S3)=0,"",IF(AND($L3<='Do not Delete'!$E$3,$M3>EOMONTH('Do not Delete'!$E$3,-12),WEEKNUM(MAX($L3,EOMONTH('Do not Delete'!$E$3,-12)+1))<=VALUE(AE$2),WEEKNUM(MIN($M3,'Do not Delete'!$E$3))>=VALUE(AE$2)),$S3,""))

 

[gd6noob]

I think maybe Im not doing this right, but the above formula, dates in 2020 do not fill in the weeks column.

 

[jasonb75]

Based on the date in SEE Status B2 and Do Not Delete E3, I assumed that is was a a 2019 sheet and set it up based on that.

The date in E3 sets the end point of the date range to be used, e.g. 31/12/19 sets the range to 1/1/19-31/12/19

 

[gd6noob]

Ahh yes.. I thought Do Not Delete E2 was the max end year... after changing E3 from 2019 to 2020, its working perfect.. I tried to enter start date as 10/1/2019 to 2/1/2020 and only 2020 weeks column is showing...

Thank you again for the help.