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

Question

sorry for my ignorance I have no idea about spreadsheets.

What I have is a column that contains dates but they are there in different format some are text like '19.12.2006 and others are cells that are formated as a date like 2009-10-10

I want them all to be date cells, so I found a formula =DATEVALUE

What I done is I put =IF(A2="";"";DATEVALUE(A2))

For the dates in text format it done well, for empty cells it's ok as well, but when the cell is a date cell I have Err: 502

Can anyone please help me?

Answer 1

As you've discovered, DATEVALUE expects a string that looks like a date, not an actual date. However, there is nothing stopping you from taking your dates and converting them to strings with TEXT. The right format mask can distinguish between dates and string dates for you.

        

The formula in N1 is,

=IF(ISBLANK(M1); ""; DATEVALUE(TEXT(M1; "mmm dd, yyyy;@")))

This formats anything that is an actual date into a string date and leaves any string dates alone. There seemed to be a dd/mm/yy vs mm/dd/yy issue between your sample data and my regoinal system so I used that complex date format but just about any date format will do. The @ is stuck on the end to catch strings. The result is processed by DATEVALUE. I used ISBLANK for the second test but there is nothing wrong with IF(M1=""; ....

Answer 2

I'm not a user of OpenOffice Calc, but in Excel and Google Sheets there is a formula called =IFERROR() which can be used in this scenario - your formula in N7 would be:

=IFERROR(IF(M7="","",DATEVALUE(M7)),M7)

Otherwise, you could use IF() with ISERROR() as follows:

=IF(ISERROR(DATEVALUE(M7)),M7,IF(M7="","",DATEVALUE(M7)))

Hope that helps!

Mike