spreadsheet - Convert text cells to DATE, but leave empty and DATE cells intact -


sorry ignorance have no idea spreadsheets.

what have column contains dates there in different format text '19.12.2006 , others cells formated date 2009-10-10

i want them date cells, found formula =datevalue

what done put =if(a2="";"";datevalue(a2))

for dates in text format done well, empty cells it's ok well, when cell date cell have err: 502

can please me?

here's looks like

as you've discovered, datevalue expects string looks date, not actual date. however, there nothing stopping taking dates , converting them strings text. right format mask can distinguish between dates , string dates you.

        calc datevalue text

the formula in n1 is,

=if(isblank(m1); ""; datevalue(text(m1; "mmm dd, yyyy;@")))

this formats actual date string date , leaves string dates alone. there seemed dd/mm/yy vs mm/dd/yy issue between sample data , regoinal system used complex date format date format do. @ stuck on end catch strings. result processed datevalue. used isblank second test there nothing wrong if(m1=""; ....


Comments

Popular posts from this blog

matlab - "Contour not rendered for non-finite ZData" -

delphi - Indy UDP Read Contents of Adata -

javascript - Any ideas when Firefox is likely to implement lengthAdjust and textLength? -