Excel: how to convert "27.11.2007 15:13" to number (cellformat fai

[dominico23]

In Excel, I have a very long list of entries showing a date and a time in the
same cell (e.g. 27.11.2007 15:13, date is in European date format). I would
like to convert each cell to a number so I can perform calculations.

However, using the Menu: FORMAT -> CELL -> NUMBER does convert it to a number.

Note: If I input the date and time by hand in the same cell then formatting
as outlined above works.

Can you help?

 

[Bob Phillips]

Select the column and then do a Replace (Ctrl-H) and replace . with /

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Dave Peterson]

If you format the cells as General and see a number, then those values are
already dates and times.

You should be able to do your calculations directly with that data.

You may want to double check your range, though.

I'd use a couple of formulas like:

=counta(a2:a999)
and
=count(a2:a999)

(Where a2:a999 is the range that contain those values.)

If those formulas evalate to the same number, then it sounds like everything is
ok for you.

Another way to double check the entries is to give the range an unambiguous date
format:
mmmm dd, yyyy hh:mm:ss

If you see values that don't change, then those cells are text and you've got
problems.

One of the biggest problems will be a mixture of mdy and dmy values. If some
are dates and some are not dates, then you may have some bad values in your
data.

01.02.2007
could be seen as January 2, 2007 or February 1, 2007.

In those cases, I'd go back to the original source to see what's correct and
what isn't.