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.

 

[gd6noob]

Thank you for the quick response.

Looking forward to your results.

 

[jasonb75]

I found 2 things causing the slowdown, the biggest cause was bloat in the SEE Status sheet caused by the pre-filled formatting that you had applied to the sheet, effectively making excel think that you have over 1 million rows of data.

Usually this can be fixed by selecting the empty rows, right click, delete entire row and save. For some reason there is a change that you have made which was preventing this from working. In a previous thread it was caused by a custom row height, I tried resetting that to default but it still would not clear, I had to resort to moving the actual data to a new sheet.

The second cause was the SUMIFS and COUNTIFS formulas in the Dashboard sheet, I couldn't do anything with that as it was locked so again, I've created a new one to rectify the problem.

I've also formatted the SEE Status sheet as a structured table, meaning that formatting, formulas etc are automatically filled down as you add new data, there is no need to pre-fill anything and bloat the sheet. You might need to change your entry method slightly as the data validation dropdowns will not show until you enter one of the manual fields, e.g. EMP ID.

The formulas in the new dashboard are also dynamic, linked to the table so will only be looking at the actual data instead of over 1 million empty rows.

Filtering and is now as close to instant as it will get, as is saving due to the file size being reduced by 85-90%.

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[gd6noob]

Thank you kind sir, I did try to delete all the rows that had no data but the formulas filled down but I didn't see much results. The rest of the issues you found, I would have not figured out.

Just took a quick look and it does indeed load fast.. Also, I noticed the hours for the weeks column are not populating.. Is this because it's a table format and not a range?

 

[jasonb75]

Also, I noticed the hours for the weeks column are not populating.. Is this because it's a table format and not a range?

The formulas should still work but might need some minor changes, I'll check through them again later.

 

[jasonb75]

I've corrected the formulas in the week columns and the period end column, couldn't see any others that needed changing.

I noticed that there were some anomalies in the week column formulas and some results that 'looked wrong' in your original file, so I've made them a bit more consistent. Your formula appeared to be comparing details in the wrong rows and incorrectly comparing numbers to blanks. If you compare the results from week 36 onward you will see the differences.

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[gd6noob]

This is awesome.. Works so much better and faster..

Just want to confirm, if the dates started in 2019 and ends in 2020 won't be counted in the weeks column?

 

[jasonb75]

Best way to check would be to enter some test dates, I 'fixed' a couple of things that didn't look right (in my opinion) but didn't make any changes to the functionality of the formulas.

 

[gd6noob]

Everything looked and functioned great, but did noticed when I played around with dates that started in 2019 and ends in 2020 didn't pick up in the weeks column.. If that's the case, I might beable to work around it, as the numbers would skew the data.. As the hours wouldn't be the same year...

 

[jasonb75]

I see what you mean, the weeknum checks will not pick it up because the start week is greater than the end week.

Am I correct in thinking that dates starting in 2019 and ending in 2020 should only be picked up at the start of the year, while dates starting in 2020 and ending in 2021 should be picked up at the end?

I'm a bit lost as to what the correct date range is, the formulas are showing results for 2020, but the dates in 'Do not delete' and the heading in B1 of SEE Status imply that it should be 2019?

I'll have a look at it after you confirm or correct my assumption above, I might not be able to get it done today though.

 

[gd6noob]

I was thinking of not having weeks columns and on the dashboard tab have something like this is possible?

	Previous year	current year	next year
Week 1
Week 2
Week 3