At my beloved workplace we’ve developed a fondness for slashes in institution names. If we’d been in charge, it would be Bachmann/Turner Overdrive, the Mason/Dixon line, Obsessive/Compulsive Disorder and well, you get the idea. Aesthetics aside, this causes problems when generating workbooks based on these names. Windows doesn’t like slashes in its file handles, because it uses them as path separators (except when it doesn’t, which is why you should use Application.PathSeparator in your code).

I recently wrote a little function to strip slashes and other shady characters out of file names and replace them with dashes, which don’t stick in Windows’ craw. Up until then, my co-worker was forced to do a search and replace on the offending monikers. In doing so she pointed out that the replace dialog mistakenly says it’s replacing all the slashes in the dates on the sheet as well, even though they remain in place.

For example, take a column like this, with one name and 1,048,574 dates:

And here it is with Excel proclaiming to have replaced 2,097,149 “/”s with “-“s, when clearly it only replaced the one. Silly old Excel!

I theorized that Excel was doing a two-step operation: first counting the slashes – including those in the dates – but then only replacing the non-date ones. I can certainly see myself doing something like that. We figured this was because Excel stores dates as numbers and the human/readable dates such like “11/15/2013” are just formats, and the slashes aren’t “really” there.

Turns out that’s wrong. I should have given Excel more credit. It really does replace the slashes if you ask it to. For example, try a find and replace with explanation points, or “bangs” as some folks call them. I get this:

What seems to happen with dashes is Excel recognizes them as date separators, but, at least on a computer with the slash set as the default date separator, it turns them right back into slashes. You can see this in it’s purest form by typing 11-15-2013 into a cell. It immediately converts the dashes, both in the formula bar and in the cell (at least if you’re using my laptop. I haven’t tested this extensively).

So it really is replacing all those millions of slashes with dashes. But then it converts them right back, without so much as a “by your leave.”

What’s confusing to me is that if I use a custom format like “yyyy.mm.dd” and try to replace the periods, Excel says there’s nothing to replace. That makes sense as the periods are just part of a format. But so are the slashes. What gives?

Speaking of dates, happy anniversary to my wonderful wife!