General discussion


Column of dates one year apart locks up Excel

By tim ·
Today a Facebook friend posted a comment to the effect that October 2011 had five Saturdays, Sundays and Mondays, and that this occurred only every 823 years. I knew that wasn't true--it happens any time October 1st falls on a Saturday. I wanted some examples of other years when this happened, and would happen, so I tried to create an Excel spreadsheet showing me what years had October 1 fall on a Saturday. I eventually got my answer, but not before having Excel (2010 version) crash several times. I'm now wondering if anyone else has this problem and/or can duplicate it, and if so, tell me why the problem is occurring.

I started with formatting Column A as Short Date (e.g. mm/dd/yyyy) and entering 10/1/2095 in A1, 10/1/2094 in A2, and 10/1/2093 in A3. I then selected A1:A3 and used the drag handle on the lower left corner of the selection box to drag down to A195 and fill
A4:A195 with 10/1/2092 to 10/1/1900.

The plan was then to place in B1 the formula =WEEKDAY(A1,1) , which returns the value of 7 (1=Sunday, 7=Saturday), and copy that formula from B1 to B2:B195 to show the days of the week for the dates corresponding in Column A. Before I could finish entering the formula in B1, the program locked up on me and would not recover. I tried this several times with the same result.

Now, I know (now) I could have just set Column A to Long Date format, and A1 would have displayed "Saturday, October 01, 2095," giving me the information I wanted. I'm still wondering what caused the program to crash, however. This doesn't strike me as an especially arduous calculation.

Excel 2010 (64 bit) with all current updates and service packs is running under Windows 7 Pro 64-bit on a Core i7 920@2.67GHz with 6GB of RAM.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Related Discussions

Related Forums