The simplest solution is to put yy,mm,dd into the date() formula by first extracting them with left(), mid() and right().
a simple popup format and some basic logic to reformat the dates would not be too difficult. To avoid swaping your regional settings back and forth you may consider writting a macro in excel to paste the data in. Which is "yyyy-MM-dd", this format is recognized the same way on every computer I have ever seen (is often refered to as ODBC format or Standard format) where the units are always from greatest to least weight ("yyyy-MM-dd HH:mm:ss.fff") another side effect is it will sort correctly as a string. The best way to import data, that contains dates, into excel is to copy it in this format. If you system is set to Canadian English/French format then it will expect "d/m/yy" format and not recognize any date where the month is > 13. it pastes correctly in my excel because I have my regional setting set to "US English" (even though I'm Canadian :) ) Your specific example is due to your list of dates is formatted as "m/d/yy" which is US format. When it is able to match your pasted in data to a valid date then it will format it as a date in the cell it is in.
When you paste data into excel it is only a bunch of strings (not dates).Įxcel has some logic in it to recognize your current data formats as well as a few similar date formats or obvious date formats where it can assume it is a date. This is caused by the regional settings of your computer.
0 kommentar(er)
