Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculate difference between two times
I have tried many different formats and formulas and the worksheet will not
calculate the difference between two times formatted as follows. 6/12/2006 8:03:14 PM 6/12/2006 8:08:55 PM #VALUE! B8 C8 the formula I used is =C8-B8 and I continue to get #VALUE!!!!!!!!! I have read the Excel help and it did not help. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculate difference between two times
hi Ken, is it formated as text?
i have been tryed id here and works =00:05:41 hth regards from Brazil Marcelo "ken" escreveu: I have tried many different formats and formulas and the worksheet will not calculate the difference between two times formatted as follows. 6/12/2006 8:03:14 PM 6/12/2006 8:08:55 PM #VALUE! B8 C8 the formula I used is =C8-B8 and I continue to get #VALUE!!!!!!!!! I have read the Excel help and it did not help. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculate difference between two times
Check and make sure that the entries are in fact real Excel dates/times.
Try these tests: =ISNUMBER(B8) =ISNUMBER(C8) Both should return TRUE. If either or both return FALSE.... Select either B8 or C8 Goto the menu DataText to Columns Select Delimited Click Finish Repeat for the other cell if needed. That might convert the entries to real dates/times. Biff "ken" wrote in message ... I have tried many different formats and formulas and the worksheet will not calculate the difference between two times formatted as follows. 6/12/2006 8:03:14 PM 6/12/2006 8:08:55 PM #VALUE! B8 C8 the formula I used is =C8-B8 and I continue to get #VALUE!!!!!!!!! I have read the Excel help and it did not help. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculate difference between two times
Hi Marcel and Biff
i have tried both of your suggestions and still I get #VALUE!. i have tried typing in the time manually and still nothing. i am running out of ideas. "Biff" wrote: Check and make sure that the entries are in fact real Excel dates/times. Try these tests: =ISNUMBER(B8) =ISNUMBER(C8) Both should return TRUE. If either or both return FALSE.... Select either B8 or C8 Goto the menu DataText to Columns Select Delimited Click Finish Repeat for the other cell if needed. That might convert the entries to real dates/times. Biff "ken" wrote in message ... I have tried many different formats and formulas and the worksheet will not calculate the difference between two times formatted as follows. 6/12/2006 8:03:14 PM 6/12/2006 8:08:55 PM #VALUE! B8 C8 the formula I used is =C8-B8 and I continue to get #VALUE!!!!!!!!! I have read the Excel help and it did not help. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculate difference between two times
I think that I have finally figured it out. When I manually enter the data
it works. I will have to write a macro or something to convert from string to date format. "Biff" wrote: Check and make sure that the entries are in fact real Excel dates/times. Try these tests: =ISNUMBER(B8) =ISNUMBER(C8) Both should return TRUE. If either or both return FALSE.... Select either B8 or C8 Goto the menu DataText to Columns Select Delimited Click Finish Repeat for the other cell if needed. That might convert the entries to real dates/times. Biff "ken" wrote in message ... I have tried many different formats and formulas and the worksheet will not calculate the difference between two times formatted as follows. 6/12/2006 8:03:14 PM 6/12/2006 8:08:55 PM #VALUE! B8 C8 the formula I used is =C8-B8 and I continue to get #VALUE!!!!!!!!! I have read the Excel help and it did not help. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculate difference between two times
Ken,
try to format the B8 as number it should return 38880,8355787037 do the same to C8 will be 38880,839525463 si this works ? regards "ken" escreveu: Hi Marcel and Biff i have tried both of your suggestions and still I get #VALUE!. i have tried typing in the time manually and still nothing. i am running out of ideas. "Biff" wrote: Check and make sure that the entries are in fact real Excel dates/times. Try these tests: =ISNUMBER(B8) =ISNUMBER(C8) Both should return TRUE. If either or both return FALSE.... Select either B8 or C8 Goto the menu DataText to Columns Select Delimited Click Finish Repeat for the other cell if needed. That might convert the entries to real dates/times. Biff "ken" wrote in message ... I have tried many different formats and formulas and the worksheet will not calculate the difference between two times formatted as follows. 6/12/2006 8:03:14 PM 6/12/2006 8:08:55 PM #VALUE! B8 C8 the formula I used is =C8-B8 and I continue to get #VALUE!!!!!!!!! I have read the Excel help and it did not help. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculate difference between two times
"ken" wrote in message
... I think that I have finally figured it out. When I manually enter the data it works. I will have to write a macro or something to convert from string to date format. You shouldn't need a macro. After you've formatted the cells as dates, put a zero in a spare cell, copy, select the cells you want turning to dates, then paste special/ add. -- David Biddulph |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I calculate times each word occurs in a column of cells? | Excel Worksheet Functions | |||
how do I calculate minutes when given two times? | Excel Worksheet Functions | |||
Calculate Difference b/e 2 dates. | New Users to Excel | |||
Difference between 2 times and dates | Excel Worksheet Functions | |||
how do i calculate minutes between two times? | Excel Discussion (Misc queries) |