formulas using date/time formats returning #value

[Guest]

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?

 

[Biff]

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

 

[Guest]

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]

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 Data>Text to Columns
Click Next,Next,Finish

Another option:

Select an empty cell that has not been preformated.
Copy that cell by going to Edit>Copy.
Now select one of the date/time cells
Then do Edit>Paste Special>Add>OK

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

 

[Guest]

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]

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

 

[Guest]

Hi

This didn't work either - can't attach a copy of the data file - best I
could do is a copy and paste of the data from excel. Do you have any more
ideas?

Logs for last calendar month
Time of Action Action Downtime
5/31/2006 8:07:00 AM UP 0:01:00
5/31/2006 8:06:00 AM DOWN
5/30/2006 10:15:00 PM UP
5/30/2006 10:14:00 PM DOWN
5/30/2006 5:10:00 PM UP
5/30/2006 5:09:00 PM DOWN
5/28/2006 8:01:00 AM UP
5/28/2006 8:00:00 AM DOWN
5/26/2006 6:42:00 PM UP
5/26/2006 6:41:00 PM DOWN
5/23/2006 8:33:00 PM UP
5/23/2006 8:25:00 PM DOWN
5/23/2006 8:14:00 PM UP
5/23/2006 7:48:00 PM DOWN
5/22/2006 7:40:00 AM UP
5/22/2006 7:39:00 AM DOWN
5/21/2006 8:06:00 AM UP
5/21/2006 8:04:00 AM DOWN
5/19/2006 8:05:00 AM UP
5/19/2006 8:04:00 AM DOWN
5/19/2006 7:51:00 AM UP
5/19/2006 7:50:00 AM DOWN
5/18/2006 3:01:00 AM UP
5/18/2006 3:01:00 AM DOWN
5/14/2006 9:44:00 PM UP
5/14/2006 9:43:00 PM DOWN
5/13/2006 12:33:00 AM UP
5/13/2006 12:32:00 AM DOWN

 

[Biff]

You can send the file to me. I don't necessarily need the whole file, just
the sheet where this data is located.

My addy is:

xl can help at comcast period net

Remove "can" and change the obvious.

Biff