set_number_format('m/d/yyyy') is formatting date to m/d/yy instead of m/d/yyyy

[amfazlani]

Hi thank you for this great gem. I had a quick question regarding date format 'm/d/yyyy'. It doesnt seem to be working as expected because when I use this number format the actual display date in excel shows as '4/4/16' instead of '4/4/2016'. Essentially the year is 2 digits instead of the expected four. This is my code: c = @sheet3.add_cell(index+1,13) c.set_number_format('m/d/yyyy') c.change_contents(student.created_at.to_date)

[weshatheleopard]

When we try to register a new number format, RubyXL attempts to re-use an existing number format first. There are some system number formats of which format number 14 is exactly what you give it, so it picks up that number as a match. However, that is a very special, regional format that is not set in stone — its presentation changes depending on your system format (the one you set up by choosing "Change the date, time or number format" item in your computer's Control Panel — and that's exactly what you observe.

It looks like Excel is doing exactly the same thing if you attempt to use that format: it changes the data to local setting. So if you want to prevent that from happening, use 'm\/d\/yyyy' instead of straight 'm/d/yyyy' (\ is an escape character which in this case does not do anything, but prevents number format 14 from being picked).