A date which is pasted from some other source is not taken as date

Question

In Excel 2016 when I type date as "May 02, 2008" it is taken as date but when I paste it (the same "May 02, 2008") it is not, Why?

Answer 1

I tried to comment but apparently since I am new here I can only answer a question, not comment on one? Weird, but it is what it is I guess. The forums I used to frequent would consider it rude to answer when offering a suggestion or possible work around, such as the one below.

I'm not sure why this is happening. It is not happening to me when I duplicate it on my PC / Excel 2016 - default settings.

I tried opening a blank workbook. I entered the same date you did, the same as you did - first type it out, then copy it from another source (notepad) - May 02, 2008 - and then I checked the cells formatting properties and in each case the cell was not formatted using the DATE category but rather the CUSTOM category with the following format - "d-mmm-yy" (so May 2, 2008 turned into 2-May-08) I then used the field labelled "TYPE:" and entered "mmm dd,yyyy" to finally get your required formatting and added the formatting type to the list of possible custom formatting types.

Oddly enough, when I selected the cell with my date formatting applied to it and change it to use the excel DATE (function?) and selected the "March 14, 2012" entry within, the only thing that changed from my formatting was the leading zero in the day was dropped leaving May 2, 2008 instead of May 02, 2008. I'm not nearly an excel expert so thats my input, for what it's worth.

I hope this helps, even though it doesn't really explain why this doesn't happen programmatically.