Calculating lost hours per week

gd6noob

Board Regular
Joined
Oct 20, 2017
Messages
170
Office Version
  1. 2016
Platform
  1. Windows
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.
1590803051851.png

The reference to the Do Not Delete tab is
1590803355013.png


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.
 

Attachments

  • 1590802928938.png
    1590802928938.png
    31.9 KB · Views: 52
  • 1590803176797.png
    1590803176797.png
    10.6 KB · Views: 53

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
There is nothing inefficient in that formula, assuming 1 row per employee I can't see that formula being the cause of the problem unless you've recruited the entire world population.
 
Upvote 0
I have about 600 rows at the moment, and I can see the slowdown, i.e. filtering rows.
Among the columns for weeks 1-52, I have other columns that also have formulas.

I was thinking, on a separate sheet, I would have 2 columns. Column A for weeks 1-52 and column would calculate all the hours, instead of 1 row per employee. Would this be more efficient?
 
Upvote 0
600 rows with that formula is not enough to cause a problem. What formulas do you have in the other columns?

Anything with VLOOKUP, MATCH, OFFSET, INDIRECT, TODAY() SUMIFS, COUNTIFS, or similar?
 
Upvote 0
600 rows with that formula is not enough to cause a problem. What formulas do you have in the other columns?

Anything with VLOOKUP, MATCH, OFFSET, INDIRECT, TODAY() SUMIFS, COUNTIFS, or similar?
Yes, I have a few columns that checks if their employee status and type of accommodation is allowed, so it has Vlookup and Today() formulas.
 
Upvote 0
TODAY() is volatile, which means it recalculates after every change, as does any formula that is linked to a cell with a TODAY() formula.
VLOOKUP with exact match is noticeably slow, especially if the lookup range uses entire columns. If formulas using TODAY() are affecting your VLOOKUP formulas in any way that will most likely account for the vast majority of the delay that you are seeing.
 
Upvote 0
TODAY() is volatile, which means it recalculates after every change, as does any formula that is linked to a cell with a TODAY() formula.
VLOOKUP with exact match is noticeably slow, especially if the lookup range uses entire columns. If formulas using TODAY() are affecting your VLOOKUP formulas in any way that will most likely account for the vast majority of the delay that you are seeing.
Thank you for the tip, is there another way around instead of using today() that is less volatile?
 
Upvote 0
The link below will explain volatile function in more detail. You can't make today() less volatile, you can only limit the amount of formulas that use it. There is not really much I can do without seeing the formulas and how they relate to each other.

 
Upvote 0
I've found a couple of potential causes for the delay that you're seeing, for some reason the 'fix' for the biggest issue is not working as it should. Same problem happened a while ago in another thread, but I can't remember the cause. I'll have a look for that in the morning.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,210
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top