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?
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.
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
Post a Comment