I'm a long time user of Excel, right back to Windows 3.11 days, and I've created some spreadsheets over the years that have been clunky, full of likely unnecessarily complex formulas. And yet I've never come across this issue before - where Excel hangs when trying to complete a simple operation such as deleting a row or copying a formula.

These days I really only use Excel for personal reasons. I have a small spreadsheet that I use to project our household's income. It is a workbook of six worksheets, with nothing particularly out of the ordinary. The first sheet is the projection, taking data from four of the other five sheets. The second sheet calculates future payment dates based on data I enter. For example, if I say that Rent needs to be paid every month on the 15th, with a -1 in an adjacent column to signify that it must be paid on the last workday before then if the 15th falls on a non-workday. This sheet then calculates the next 13 payment dates for the rent using this formula in 13 cells across the same row:

=WORKDAY(DATE(YEAR(Projection!$C$5),MONTH(Projection!$C$5)+(H$2-1),($D13-$C13)),$C13,Hols)

Projection!$C$5 is simply the date my projection begins, usually the current day or a recent day.
H$2 is the month number of that column (all calculated dates in that column are for the same month).

$D13 is the normal payment date, for example 15 to represent the 15th.

$C13 is either -1 or 1, signifying an earlier or later working day should be given if $D13 is a non-workday.

Hols refers to a list of dates on a different sheet that represent public holidays.

This formula appears up to 228 times on the sheet, which is 20 rows of credits/debits and 13 calculated dates for each one. It sounds a lot but it's not a complex formula.

This sheet is the one that's causing the problem. The whole workbook works and acts as expected, but this particular sheet takes a second or two to appear if I click its tab. But the issues it causes are much wider - it causes Excel to hang if I try and copy/paste or insert or delete columns/rows on any of the sheets in the book, even the sixth which isn't linked to any of the others in any way.

I've been through three levels of Microsoft technical support and all they've done is follow the same guide to try and chase down a bug in the app. I have repaired, reinstalled, removed all addons, used safe mode, etc. Nothing helps. This issue only started happening a few weeks ago but it got steadily worse and now makes Excel unusable. There are no new programs on my PC save the ones that I've installed at the behest of Microsoft support.

My PC has 16GB of RAM and 50+ GB of HDD free.

Any ideas? This is driving me mad.

Forum Discussion

Workday function causing Excel to hang