Hi

Am dumping data out of an application - raw data appears as
5/26/2006 5:33:00 AM (i.e. mm/dd/yyyy hh:mm:ss AM/PM) - format all the
cells in custom format to this. However when I go to calculate the
difference between two times (elapsed time) returns #VALUE.
5/26/2006 5:33:00 AM less 5/26/2006 5:26:00 AM s/b 07:00

It's probably something very simple - can someone help please?

Hi!

Test the dates/times to make sure they are in fact real Excel dates/times.

If you have these cell values:

A1 = 5/26/2006 5:33:00 AM
B1 = 5/26/2006 5:26:00 AM

Try this test:

=ISNUMBER(A1)
=ISNUMBER(B1)

If both cells are real Excel dates/times then those Isnumber formulas will
return TRUE and a simple subtraction formula should work without returning
an error.

Biff

"Liesel" wrote in message
...
Hi

Am dumping data out of an application - raw data appears as
5/26/2006 5:33:00 AM (i.e. mm/dd/yyyy hh:mm:ss AM/PM) - format all the
cells in custom format to this. However when I go to calculate the
difference between two times (elapsed time) returns #VALUE.
5/26/2006 5:33:00 AM less 5/26/2006 5:26:00 AM s/b 07:00

It's probably something very simple - can someone help please?

Hi

Have tested and the cells are returning FALSE.

Next question, how do I turn them into a format that Excel will recognise as
real Excel dates/times?

Appreciate your assistance

"Biff" wrote:

Hi!

Test the dates/times to make sure they are in fact real Excel dates/times.

If you have these cell values:

A1 = 5/26/2006 5:33:00 AM
B1 = 5/26/2006 5:26:00 AM

Try this test:

=ISNUMBER(A1)
=ISNUMBER(B1)

If both cells are real Excel dates/times then those Isnumber formulas will
return TRUE and a simple subtraction formula should work without returning
an error.

Biff

"Liesel" wrote in message
...
Hi

Am dumping data out of an application - raw data appears as
5/26/2006 5:33:00 AM (i.e. mm/dd/yyyy hh:mm:ss AM/PM) - format all the
cells in custom format to this. However when I go to calculate the
difference between two times (elapsed time) returns #VALUE.
5/26/2006 5:33:00 AM less 5/26/2006 5:26:00 AM s/b 07:00

It's probably something very simple - can someone help please?

Here's a couple of things to try:

Try on one cell and if it works then do it on all the cells in question:

Select a cell with a date/time
Goto DataText to Columns
Click Next,Next,Finish

Another option:

Select an empty cell that has not been preformated.
Copy that cell by going to EditCopy.
Now select one of the date/time cells
Then do EditPaste SpecialAddOK

If it worked then the TEXT value 5/26/2006 5:33:00 AM should have been
converted to the decimal value 38863.23125. Then you can reformat as a real
date/time in the format of your choice.

Biff

"Liesel" wrote in message
...
Hi

Have tested and the cells are returning FALSE.

Next question, how do I turn them into a format that Excel will recognise
as
real Excel dates/times?

Appreciate your assistance

"Biff" wrote:

Hi!

Test the dates/times to make sure they are in fact real Excel
dates/times.

If you have these cell values:

A1 = 5/26/2006 5:33:00 AM
B1 = 5/26/2006 5:26:00 AM

Try this test:

=ISNUMBER(A1)
=ISNUMBER(B1)

If both cells are real Excel dates/times then those Isnumber formulas
will
return TRUE and a simple subtraction formula should work without
returning
an error.

Biff

"Liesel" wrote in message
...
Hi

Am dumping data out of an application - raw data appears as
5/26/2006 5:33:00 AM (i.e. mm/dd/yyyy hh:mm:ss AM/PM) - format all the
cells in custom format to this. However when I go to calculate the
difference between two times (elapsed time) returns #VALUE.
5/26/2006 5:33:00 AM less 5/26/2006 5:26:00 AM s/b 07:00

It's probably something very simple - can someone help please?

Hi

Tried both options, but no change and am still unable to make calculations
with the data (still returning false to =isnumber).

Can you think of anything else?

"Biff" wrote:

Here's a couple of things to try:

Try on one cell and if it works then do it on all the cells in question:

Select a cell with a date/time
Goto DataText to Columns
Click Next,Next,Finish

Another option:

Select an empty cell that has not been preformated.
Copy that cell by going to EditCopy.
Now select one of the date/time cells
Then do EditPaste SpecialAddOK

If it worked then the TEXT value 5/26/2006 5:33:00 AM should have been
converted to the decimal value 38863.23125. Then you can reformat as a real
date/time in the format of your choice.

Biff

"Liesel" wrote in message
...
Hi

Have tested and the cells are returning FALSE.

Next question, how do I turn them into a format that Excel will recognise
as
real Excel dates/times?

Appreciate your assistance

"Biff" wrote:

Hi!

Test the dates/times to make sure they are in fact real Excel
dates/times.

If you have these cell values:

A1 = 5/26/2006 5:33:00 AM
B1 = 5/26/2006 5:26:00 AM

Try this test:

=ISNUMBER(A1)
=ISNUMBER(B1)

If both cells are real Excel dates/times then those Isnumber formulas
will
return TRUE and a simple subtraction formula should work without
returning
an error.

Biff

"Liesel" wrote in message
...
Hi

Am dumping data out of an application - raw data appears as
5/26/2006 5:33:00 AM (i.e. mm/dd/yyyy hh:mm:ss AM/PM) - format all the
cells in custom format to this. However when I go to calculate the
difference between two times (elapsed time) returns #VALUE.
5/26/2006 5:33:00 AM less 5/26/2006 5:26:00 AM s/b 07:00

It's probably something very simple - can someone help please?

OK, this is a stubborn one!

If this data is imported from another application chances are there are
unseen characters in the string that's causing the problem. You can try this
in some cell, say, J1:

=TRIM(CLEAN(A1))+0

Then:

=ISNUMBER(J1)

See what that does!

If that doesn't get you anywhere, try this macro. It removes unseen "junk"
that sometimes tags along with imported data:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

If all else fails, see if these do anything:

=DATEVALUE(A1)
=TIMEVALUE(A1)
=DATEVALUE(A1)+TIMEVALUE(A1)

If everything above fails I'm pretty much out of ideas and would need to see
this for myself to try and figure it out!!!!

Biff

"Liesel" wrote in message
...
Hi

Tried both options, but no change and am still unable to make calculations
with the data (still returning false to =isnumber).

Can you think of anything else?

"Biff" wrote:

Here's a couple of things to try:

Try on one cell and if it works then do it on all the cells in question:

Select a cell with a date/time
Goto DataText to Columns
Click Next,Next,Finish

Another option:

Select an empty cell that has not been preformated.
Copy that cell by going to EditCopy.
Now select one of the date/time cells
Then do EditPaste SpecialAddOK

If it worked then the TEXT value 5/26/2006 5:33:00 AM should have been
converted to the decimal value 38863.23125. Then you can reformat as a
real
date/time in the format of your choice.

Biff

"Liesel" wrote in message
...
Hi

Have tested and the cells are returning FALSE.

Next question, how do I turn them into a format that Excel will
recognise
as
real Excel dates/times?

Appreciate your assistance

"Biff" wrote:

Hi!

Test the dates/times to make sure they are in fact real Excel
dates/times.

If you have these cell values:

A1 = 5/26/2006 5:33:00 AM
B1 = 5/26/2006 5:26:00 AM

Try this test:

=ISNUMBER(A1)
=ISNUMBER(B1)

If both cells are real Excel dates/times then those Isnumber formulas
will
return TRUE and a simple subtraction formula should work without
returning
an error.

Biff

"Liesel" wrote in message
...
Hi

Am dumping data out of an application - raw data appears as
5/26/2006 5:33:00 AM (i.e. mm/dd/yyyy hh:mm:ss AM/PM) - format all
the
cells in custom format to this. However when I go to calculate the
difference between two times (elapsed time) returns #VALUE.
5/26/2006 5:33:00 AM less 5/26/2006 5:26:00 AM s/b 07:00

It's probably something very simple - can someone help please?