|
formulas using date/time formats returning #value
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? |
formulas using date/time formats returning #value
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? |
formulas using date/time formats returning #value
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? |
formulas using date/time formats returning #value
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? |
formulas using date/time formats returning #value
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? |
formulas using date/time formats returning #value
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? |
formulas using date/time formats returning #value
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" wrote: 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? |
formulas using date/time formats returning #value
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 "Liesel" wrote in message ... 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" wrote: 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? |
| All times are GMT +1. The time now is 09:49 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com