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)

 

[pagrender]

Re: calculate date difference adn return hours

Welcome to the Board.

Does this formula work for you?
=TEXT(ROUNDDOWN((A1-B1)*1440/60,0),"0")&" h"&" "&TEXT(MOD((A1-B1)*1440,60),"00")& "m"

 

[T. Valko]

Re: calculate date difference adn return hours

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)

Maybe this...

=A1-B1

Format as [h]:mm

Copy down as needed.

 

[manohar.ra]

Re: calculate date difference adn return hours

thank u very much fro replying

unfortunately the solutions does nto seem to work

Help needed urgently

 

[T. Valko]

Re: calculate date difference adn return hours

thank u very much fro replying

unfortunately the solutions does nto seem to work

Help needed urgently

Here is your data with the time difference in [h]:mm format in column C:

Book1
	A	B	C
2	2/25/2011 8:04	2/25/2011 8:01	0:03
3	1/15/2011 1:45	1/14/2011 11:13	14:32
4	12/28/2010 8:55	12/28/2010 8:41	0:14
5	11/3/2010 9:43	11/3/2010 9:30	0:13
6	2/8/2011 12:00	2/8/2011 11:59	0:01
7	12/30/2010 9:47	11/24/2010 0:15	873:32
8	2/3/2011 14:57	2/3/2011 14:55	0:02
9	2/17/2011 13:34	2/15/2011 7:14	54:20
Sheet1

If that's not correct then explain what result you expect.

 

[manohar.ra]

Re: calculate date difference adn return hours

How do i convert

<TABLE style="WIDTH: 98pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=131 border=0 x:str><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-LEFT: black 0.5pt solid; WIDTH: 98pt; BORDER-TOP-COLOR: black; 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-LEFT: black 0.5pt solid; WIDTH: 98pt; BORDER-TOP-COLOR: black; 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-LEFT: black 0.5pt solid; WIDTH: 98pt; BORDER-TOP-COLOR: black; 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-LEFT: black 0.5pt solid; WIDTH: 98pt; BORDER-TOP-COLOR: black; 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-LEFT: black 0.5pt solid; WIDTH: 98pt; BORDER-TOP-COLOR: black; 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-LEFT: black 0.5pt solid; WIDTH: 98pt; BORDER-TOP-COLOR: black; 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-LEFT: black 0.5pt solid; WIDTH: 98pt; BORDER-TOP-COLOR: black; 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>
to the format that u have mentioned , it is not working when i convert to date

 

[T. Valko]

Re: calculate date difference adn return hours

How do i convert

<TABLE style="WIDTH: 98pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=131 border=0 x:str><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-LEFT: black 0.5pt solid; WIDTH: 98pt; BORDER-TOP-COLOR: black; 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-LEFT: black 0.5pt solid; WIDTH: 98pt; BORDER-TOP-COLOR: black; 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-LEFT: black 0.5pt solid; WIDTH: 98pt; BORDER-TOP-COLOR: black; 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-LEFT: black 0.5pt solid; WIDTH: 98pt; BORDER-TOP-COLOR: black; 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-LEFT: black 0.5pt solid; WIDTH: 98pt; BORDER-TOP-COLOR: black; 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-LEFT: black 0.5pt solid; WIDTH: 98pt; BORDER-TOP-COLOR: black; 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-LEFT: black 0.5pt solid; WIDTH: 98pt; BORDER-TOP-COLOR: black; 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>
to the format that u have mentioned , it is not working when i convert to date

I use U.S. English regional settings and when I copy that data and paste it into Excel it automatically gets formatted as m/d/yyyy h:mm.

 

[delaneyjm]

Perhaps the dates you have in each column are not true dates.

What is the result returned from following?

In C1, just enter:
=ISNUMBER(A1)

In D1, just enter:
=ISNUMBER(B1)

 

[manohar.ra]

Exactly , the dates the columns dont seem to contain true dates

ISNUMBER() returns false

am not able to convert this to dates , when i format the cells the dates

Any other solution?

 

[T. Valko]

Exactly , the dates the columns dont seem to contain true dates

ISNUMBER() returns false

am not able to convert this to dates , when i format the cells the dates

Any other solution?

Sometimes this will convert TEXT numbers to numeric numbers...

Select the range of cells in question
Goto Data>Text to Columns
Click Finish