calculate date difference and return hours

[manohar.ra]

I have 2 date columns

Column A
<TABLE style="WIDTH: 98pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=131 border=0 x:str><COLGROUP><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4790" width=131><TBODY><TR style="HEIGHT: 25.5pt" height=34><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 98pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent" width=131 height=34>2011-02-25 08:04:00 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 98pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=131 height=17>2011-01-15 01:45:00 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 98pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=131 height=17>2010-12-28 08:55:00 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 98pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=131 height=17>2010-11-03 09:43:00 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 98pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=131 height=17>2011-02-08 12:00:00 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 98pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=131 height=17>2010-12-30 09:47:00 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 98pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=131 height=17>2011-02-03 14:57:00 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 98pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=131 height=17>2011-02-17 13:34:00 </TD></TR></TBODY></TABLE>

column B

<TABLE style="WIDTH: 98pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=131 border=0 x:str><COLGROUP><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4790" width=131><TBODY><TR style="HEIGHT: 25.5pt" height=34><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 98pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent" width=131 height=34>2011-02-25 08:01:00 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 98pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=131 height=17>2011-01-14 11:13:00 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 98pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=131 height=17>2010-12-28 08:41:00 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 98pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=131 height=17>2010-11-03 09:30:00 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 98pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=131 height=17>2011-02-08 11:59:00 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 98pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=131 height=17>2010-11-24 00:15:00 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 98pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=131 height=17>2011-02-03 14:55:00 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 98pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=131 height=17>2011-02-15 07:14:00 </TD></TR></TBODY></TABLE>

I want to calculate the difference in hours between the two dates (ex: 68 h 38 m)

 

[manohar.ra]

Still ISNUMBER() returns false

 

[T. Valko]

Still ISNUMBER() returns false

The macro at this website will remove all leading/trailing and multiple interspersed char 32 space characters. It will also remove and/or convert char 160 non breaking spaces into standard char 32 space characters. It will work on text or numbers and the numbers will be converted to true numeric numbers. I use this macro dozens of times every single day! It is a real time saver.

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

 

[manohar.ra]

Not working

Is there a way to upload the file here so that u can have a clear picture of what the problem is ?

Valko- thanks a lot for taking time and replying , really appreciated

 

[T. Valko]

Not working

Is there a way to upload the file here so that u can have a clear picture of what the problem is ?

Valko- thanks a lot for taking time and replying , really appreciated

You can't directly upload the file to this specific site.

However, you can upload the file to some other site and then provide a link to the file if anyone wants to download it. If you don't have access to your own site or a work site there are many free file hosting sites.

However, some (many/most) folks won't open/download files from unknown sources.

Sometimes I will and sometimes I won't. It depends on what kind of "vibe" I get!

 

[manohar.ra]

http://www.ozgrid.com/forum/showthread.php?t=153941&p=555211#post555211

i have posted the file in this site , I want ot calculate the value of column J - D

 

[T. Valko]

http://www.ozgrid.com/forum/showthread.php?t=153941&p=555211#post555211

i have posted the file in this site , I want ot calculate the value of column J - D

Well, I see you're getting the same advice there as you are here.

So, there's not much more I can offer.

Good luck!

 

[manohar.ra]

The Problem was due to the chr(160)'s present in the cells ,
after eliminating chr(160)'s from teh cells by running a macro
i can get the difference easily

Thanks a lot for ur advice

 

[T. Valko]

The Problem was due to the chr(160)'s present in the cells ,
after eliminating chr(160)'s from teh cells by running a macro
i can get the difference easily

Thanks a lot for ur advice

That's what the macro I suggested in post #12 does.

Glad you finally got this resolved!

 

[manohar.ra]

am still unable to solve this problem ,

Suppose i haev two dates

<TABLE style="WIDTH: 98pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=131 border=0 x:str><COLGROUP><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4790" width=131><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 98pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=131 height=17>2011-02-17 13:34:00 </TD></TR></TBODY></TABLE>

<TABLE style="WIDTH: 98pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=131 border=0 x:str><COLGROUP><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4790" width=131><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 98pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=131 height=17>2011-02-15 07:14:00 </TD></TR></TBODY></TABLE>

the difference between the two dates is 54:20

Can someone give me a formula to get this ???

 

[T. Valko]

am still unable to solve this problem ,

Suppose i haev two dates

<TABLE style="WIDTH: 98pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=131 border=0 x:str><COLGROUP><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4790" width=131><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 98pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=131 height=17>2011-02-17 13:34:00 </TD></TR></TBODY></TABLE>

<TABLE style="WIDTH: 98pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=131 border=0 x:str><COLGROUP><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4790" width=131><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 98pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=131 height=17>2011-02-15 07:14:00 </TD></TR></TBODY></TABLE>

the difference between the two dates is 54:20

Can someone give me a formula to get this ???

Format the cell with the formula as [h]:mm

The brackets [ ] keep the hours from rolling over into days.