Array bogging down CPU

Are you still using Excel 2016?

It's your named ranges - they are full column references, so over a million rows each. I've removed them.

Use this instead:

=IF(A2 = "", "", TEXT(MIN(IF($A$2:$A$10000 = A2, $E$2:$E$10000)), "d mmm yy")&" - "&TEXT(MAX(IF($A$2:$A$10000 = A2, $E$2:$E$10000)), "d mmm yy"))

and this:

=CHOOSE(WEEKDAY(E3),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")

If you must use named ranges, limit them.

Change the named ranges "Year_week" and "Date" to be sensible maxima e.g $A1:$A6000

Please see the attached updated sample file.

I have changed all the full column Named Ranges into Dynamic Named Ranges based on column A.

For example, Year_Week:

Formula:

Please Login or Register  to view this content.

And, Weekday:

Formula:

Please Login or Register  to view this content.

Column C (Weekday) can be just =E2, formatted as "dddd" and copied down, rather than:

Formula:

Please Login or Register  to view this content.

And I've used this (slight tweak) for the range:

Formula:

Please Login or Register  to view this content.

I've left in a lot of "test columns" from J onwards that demonstrate the different elements that can be extracted and/or combined. They can all be deleted.

Calculation is immediate even with all the extra columns.

Edit: deleted - misread earlier response.

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, remember that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.